how can I count # of columns between two specific columns?

Visual Basic for Applications (VBA)

‹ Microsoft ISV
- Archive
  - ISV
  - Visual Studio
  - Windows Forms
  - VS 2008
  - Windows Vista
  - SharePoint
  - Architecture
  - Dotnet
  - VB
  - IE
  - VS Express
  - Office
  - VC++
  - Game
  - SQL Server
  - Windows Live
  - VFP
  - Smart Device
  - Visual C#

- Previous
  - 1
    - how to get the VALUE of an OUTLINENUMBER (word / macro in VB) Hi, I am currently working on a VB macro which has to get references in Word documents in order to make a traceability matrix. My problem comes from a document, in which the references are Outlinenumbers in a column containing this outlinenumber list. I have to get the value of the cells of this column, but these outlinenumbers (auto-incremented numbers) are neither strings nor numbers... I don t know the type and then I don t know how to get the value. Any idea that would be great because i m currently stopped in my internship because of that. Regards, Cal. Tag: how can I count # of columns between two specific columns?
  - 2
    - HELP FIX THE CODE.......... I have somehow managed to put some code together to complete a task that i have found some difficulty with. Match and delete a row from table. Need some help fixing the code.......... ArrNames = Array("creation1", "creation2", "creation3", "Creation4", "creation5", "creation6", "creation7", "creation8", "creation9", "creation10", "creation11") If Not IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete Dim ArrNames As Variant With .Cells(Lrow, "B") Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(399).Row Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Tag: how can I count # of columns between two specific columns?
  - 3
    - Problems with VB solver code I am writing visual basic code behind an excel spreasheet and am having difficulty making it work. It involves using the solver function. The code is listed below: Sub signif_control_pop() Dim cntlpc Dim pop 'Clear any previous solver settings. SOLVERReset 'Prompt user for population size. pop = Application.InputBox(Prompt:="Population Total:", Type:=1) Range("C3") = pop 'Set targeted cell, H16, to a value by changing the range of acceptable percentages. SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:=Range("B5") ' Solve the model and keep the final results. SolverSolve Userfinish:=False 'Save results of control percent before you discard the results. cntl_pc = Range("B5") SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1 ' Show the result in a message box. MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " " End Sub The problem I'm having is regarding the "ByChange" cell (B5) used in the solver function. The value in the cell never changes. When the message box is displayed, it always displays the same number, which is the number that was sitting in cell B5 before I ran the program. I have included "SolverFinish KeepFinal:= 1" in the code. If I do not run the visual basic code, but rather manually run Solver in excel, it will change cell as I want it to do. Any help is appreciated. Stacy Tag: how can I count # of columns between two specific columns?
  - 4
    - New to Visual Basic Hi darnold, I'm posting the modified code of yours, to achieve what you wanted.Check this,let me know if any issues with this. Dim str As String Dim Number As Integer Private Sub Form1_Load( ) str = "" End Sub Private Sub cmdOne_Click() str = str + "1" Number = CInt (str) End Sub Private Sub cmdTwo_Click() str = str + "2" Number = CInt (str) End Sub Private Sub cmdThree_Click() str = str + "3" Number = CInt (str) End Sub Private Sub cmdEnter_Click() End Sub Private Sub cmdStdBurger_Click() StdBurger = 1.99 End Sub Private Sub cmdDlxBurger_Click() DlxBurger = 2.99 End Sub Private Sub cmdSmFries_Click() SmFries = 0.99 End Sub Private Sub cmdSubTotal_Click() 'You didn't post this line completely txtSubTotal = End Sub Private Sub cmdTotal_Click() txtTax = txtSubTotal * 0.05 txtTotal = (txtSubTotal * 0.05) + txtSubTotal End Sub If you have any issues with the above code,please post the complete code without missing any line. Thanks, Ch.T.Gopi Kumar. Tag: how can I count # of columns between two specific columns?
  - 5
    - Information required : WOW64 What is WOW64 How it helps to run 32-bit applications on 64-bit processor Where could I get it Tag: how can I count # of columns between two specific columns?
  - 6
    - MSDN Installation I've MSDN installed on my system, since i've installed it through MSDN setup (not by VS setup) Visual Studio does not recognize its presence(Ctrl+F1 doesn't work.), Is there a way I could tell visual studio about the presence of MSDN. By the way this behaviour is common in both VS 2003 as well as VS2005 Tag: how can I count # of columns between two specific columns?
  - 7
    - error message when closing ie7 The computer administrator profile is not having this problem only the other users on my home computer. Upon closing the browser this pops up: iexplore.exe Application error The exception unknown software exception (0x0eedfade) occured in the application at location 0x7c812a5b We have ie7. Can someone help Tag: how can I count # of columns between two specific columns?
  - 8
    - add group to printer ACL Hello, I have a print server with 200 shared printers. I need to add a group to each printer ACL with an appropriate rights (same for all printers). Is anyway to script it Thank you Ali Tag: how can I count # of columns between two specific columns?
  - 9
    - VBA XML and encoding Hello, Here is my issue. In an excel sheet, I fill in several cells which contain text. This text can be either in english / french / spanish / italien / german, which means I can have characters like "e" or "e" etc... In a vba macro I'm trying to put all these data into an XML, this XML is then processed later on by an ASP.net app I have created (using an xmlreader). The thing is, when I try to display the value of each node of my XML with my ASP.net app, some characters are replaced by squares. For example, characters like "-", "...", singlequote BUT, characters like "e" and "e" are perfectly displayed . And when I have a look into my XML file with Visual Studio I can clearly see all my characters. Everything is fine. However, when I try to save the file in VS, it warns me "that some unicode characters can't be saved in the current codepage. Do you want to resave the file as Unicode in order to maintain your data " If I click "YES" it then set the Xml Encoding to : UTF-16 And from now on, everything is displayed correctly in my ASP.net app. Here is the VBA Code which generates my XML File. Code Snippet Open strPath & cstStudyName & ".xml" For Output As 1 Print #1, "< xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & " >" Print #1, "<root>" iCurLine = cstiStartLine While objSheet.Cells(iCurLine, cstiVarCol).Value <> "" strValue = objSheet.Cells(iCurLine, iEnglisCol) Print #1, "<" & objSheet.Cells(iCurLine, cstiVarCol) & " label= " & Chr(34) & CheckContentsXML(strValue) & Chr(34) & "/>" iCurLine = iCurLine + 1 Wend Print #1, "</root>" Close #1 As you can see, I have tried to specify "ISO-8859-1" in the XML header. If I specify "UTF-8" then xmlreader is generating an error at the first weird character it encounters such as "-" If I try to specify "UTF-16", it then becomes a bunch of unreadable characters and my ASP.net display nothing. Does anyone have an idea Thanks a lot. Tag: how can I count # of columns between two specific columns?
  - 10
    - Increase prompt in message box I have a userform that contains a command button that when clicked displays a message box. This message box contains help notes for the user. However I can't quite fit all the text in. When displayed, the message box cuts off the last couple of sentences. I think this is because my help text is the prompt in the message box and it is too long (> 1024 characters). How can I get a few extra sentences into the message box Perhaps there is a better way to display the help text Tag: how can I count # of columns between two specific columns?
  - 11
    - Configuration Failed Configuration Failed One or more configuration settings failed. Completed configuration settings will not be rolled back. Resolve the problem and run this configuration wizard again. The following contains detailed information about the failure: Failed to initialize SharePoint Products and Technologies upgrade. An exception of type System.Collections.Generic.KeyNotFoundException was thrown.Additional Information The given key was not present in the dictionary Plz Help One or more configuration settings failed. Completed configuration settings will not be rolled back. Resolve the problem and run this configuration wizard again. The following contains detailed information about the failure: Failed to initialize SharePoint Products and Technologies upgrade. An exception of type System.Collections.Generic.KeyNotFoundException was thrown.Additional Information The given key was not present in the dictionary Plz Help Tag: how can I count # of columns between two specific columns?
  - 12
    - Communicator Mobile 2005 and TLS (Using port 4443 possible?) More options Confused on something with CM and CWA: We have web access enabled/installed, it uses MTLS and certificates on port 4443 to access the site... I'm assuming when using Communicator Mobile on Windows Mobile 2005, I specify TLS to connect remotely to the server (no vpn).. Is there a way to specify port 4443 or is the TLS port 443 even with Web Access installed on port 4443 (IE: these are different or the same) Thanks Tag: how can I count # of columns between two specific columns?
  - 13
    - VBA in Excel - organizing charts as objects in a sheet I am using a macro to create multiple charts from data on a separate sheet in the Excel file. However, the program places each new chart on top of the previous one in the sheet I select. I would like to place these charts apart on the sheet so that they are viewable. 1) Is there an command to order the charts down and across the sheet 2) Alternatively, can I select and move each sheet without using the actual object name in the while loop, "Chart1," for example. 3) Specifiying the object name would only work for the first chart. Is there a way to number the charts with a variable Here is the while portion of my code as it is now ( red text indicates the portion I am having difficulty with ) : While j < 243 Charts.Add With Sheets("TRESULTSdmp_3") ActiveChart.SetSourceData Source:=.Range(.Cells(i + 1, j + 1), .Cells(i + m, j + 1)), PlotBy:=xlColumns End With ActiveChart.ChartType = xlXYScatter ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Test point to the right" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveWindow.Visible = False <----- question 2 above) want to move object to a specific location ActiveWindow.WindowState = xlNormal (will have to make Range a variable) ActiveWindow.WindowState = xlMaximized Range("A1").Select ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Paste ActiveSheet.Shapes("Chart 3").IncrementLeft -123.75 <--- question 3 above) want to make object # variable ActiveSheet.Shapes("Chart 3").IncrementTop -8.25 j = j + 1 Wend Tag: how can I count # of columns between two specific columns?
  - 14
    - AutoExec will not execute properly your findings are all correct. In the AutoExec I have now added a small delay as shown Sub AutoExec() ' ' macro is run when AddIn is read in ' the only job is to set the entry in the menubar ' ' we have to wait until Word has done its job Application.OnTime When:=Now + TimeValue("00:00:03"), _ Name:="start_macro" End Sub ' AutoExec Note: the start_macro is as follows: Sub start_macro() show_warning = True Call initialise_program Call check_if_vpn(False) ' check if connection to server exists End Sub with this delay the entries in the menubar show up correctly. The sub initialise_program is identical in Excel and PowerPoint and executes correctly also on those PC's where the Wordmacro does not. Tag: how can I count # of columns between two specific columns?
  - 15
    - Determine if and where the .NET Framework is installed using VBA %windir% is a environment variable that point to Windows instalation path in this PC. C:\Documents and Settings\ValerianoSrvr>set windir windir=C:\WINNT How a Windows system can be installed in a different PC Regards. Tag: how can I count # of columns between two specific columns?
  - 16
    - Pivot Table Macro Hi, I have set up a small macro to create a pivot table from a spreadsheet: Sub CreatePivot() ' ' CreatePivot Macro ' Macro recorded 3/14/2007 by Gary ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Data!R1C1:R656C90").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Sheets("Sheet1").Select Sheets("Sheet1").Name = "Pivot" End Sub The problem is that each spreadsheet I run it on, has a different number of rows. They all have the same number of columns (A-CL), but the number of rows varies. I'd like the pivot to work no matter how many rows it is: i.e., that it will automatically select the entire range whatever it is. Is this doable Thank you, Gary Tag: how can I count # of columns between two specific columns?
- Next
  - 1
    - Why the do while loop does not work? Hello, Below is my code, the lastrowGSVGrowthRate is 101, I expected result is to copy all the "AFFILIATE" items. While with my code, it only copied the first "AFFILIATE" item. I do not know where I did wrong. Thanks so much! B.regards/Yaping raffiliate = 1 rowaffiliate = 1 Do While raffiliate <= lastrowGSVGrowthRate If cells(raffiliate, 1) = "AFFILIATE" Then MsgBox raffiliate Range(cells(raffiliate, 1), cells(raffiliate, lastcolumnGSVGrowthRate)).Select Selection.Copy worksheets("AFFILIATE").Activate ActiveSheet.cells(rowaffiliate, 1).Select Selection.PasteSpecial Paste:=xlPasteAll rowaffiliate = rowaffiliate + 1 Else End If raffiliate = raffiliate + 1 Loop Tag: how can I count # of columns between two specific columns?
  - 2
    - VBA and automated word generation Hi there, I am currently working on a VBA application. There is an automated word generation available in the appliacation that serves the reporting process. If there is any Flash add-in being installed in the user's machine (can be viewed through Tools->Templates and Add-ins in MS Word) ,the applicaiton crashes down when the user tries to generate the report because the flash add-in runs initially before the word gets opened. Is there any work around available other than removing the add-in for the application to run successfully Your help is greatly appreciated Tag: how can I count # of columns between two specific columns?
  - 3
    - Using Excel files in MS Works Had Office and XP on my previous computer and created several Excel files I want/need to keep New computer has Vista and MS WORKS installed - will I have a problem with transferring the Excel files to new computer If so, how do I remedy Tag: how can I count # of columns between two specific columns?
  - 4
    - iexplore.exe - Application Error I discovered the problem. It was because of Yahoo Toolbar add on. I uninstalled it and now it works fine. Marty Tag: how can I count # of columns between two specific columns?
  - 5
    - error with wscript.shell error 80070483 hi, i am trying to run a vb script from a VBA module in excel. fn = "test.vbs Set wshShell = CreateObject("Wscript.Shell") wshShell.Run (fn) in the line wshShell.Run (fn) i obtain an error error 80070483 method Run of object IWshShell3 failed it is the first time i do this, so i am not shure if i need to have done something before, some references in windows extension .vbs is not related to any application (is it ok ) tks in advance Tag: how can I count # of columns between two specific columns?
  - 6
    - Obtain Unique Work Units via SQL Adamus, I am still getting 197 for total WorkUnits. Code Snippet SELECT DISTINCT Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults] FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)); What about in line four in the code above where I am dividing with /Count([WorkUnit]),2) AS [Pct NoFaults] Could this be causing the WorkUnits field to be total records and not distinct WorkUnits Tag: how can I count # of columns between two specific columns?
  - 7
    - Adding Menu to Microsoft project 2007 using VBA How can I add a custom menu using VBA code. There are some changes happened in from Microsoft Project 2003 to Microsoft Project 2007. How can I add custom menu to microsoft project 2007 and make it work to invoke appropriate code. Are there any whitepaper which talks about the changes in the coding from Microsoft project 2003 to 2007. I am new to this area of coding with little knowledge of VBA. ANy help will be highly appreciated. A sample code will work wonders for me. Tag: how can I count # of columns between two specific columns?
  - 8
    - compile error: invalid quaifier Hi! I'm a portuguese school teacher that uses vb 6.0. The error mentioned occurs here **: Public Sub introduz() i = 0 Do While i <= 20 VF.Nr(i) = Val(InputBox("Introduza o numero de cliente", "Numero de cliente")) ** VF.NomeCliente(i) = InputBox("Introduza o nome do cliente", "Nome do cliente") If VF.NomeCliente(i) = ("XYZ") Then Exit Do End If VF.Dt(i) = InputBox("Introduza a Data da Factura", "Data da Factura") i = i + i Loop End Sub That's an array defined with public statement that uses an type structure also defined with public statement. Can anyone help me My students say thanks!! Tag: how can I count # of columns between two specific columns?
  - 9
    - Collection Item: Parent / NextItem Hi, I am struggling with classes. This is what I want to do, in VBA (test it in Excel please): I want to add a property/function to my class, named NextItem and PreviousItem. These return the next and previous items in the collection. I have several separate collections where I keep my items in. So if I want to determine the next and previous item, I need to figure out in which collection the item resides before I can start to loop through that collection. Unfortunately there is no collection.item.parent. That is what I need to simulate I thought about adding a property ParentCollection to the class, but how can I use that property as a pointer to a collection and retrieve the collection from that pointer. Help! Tag: how can I count # of columns between two specific columns?
  - 10
    - VBA Language Definition Hi, just a quick question Does there exist anywhere a formal definition of the VBA language It would be really useful for my dissertation as i am writing a program that interprets macros so could do with something that would tell me, for example all the different ways that variables can be defined, or all the different types of statement that can be used etc. Any advice is much appreciated Ryan Tag: how can I count # of columns between two specific columns?
  - 11
    - Are these valid bucket ids ? DHON, You are running into trouble here because you are trying to mimic an event type. If I understand the problem you are really trying to solve, it��s getting your application��s crash reports on Winqual. All you need to do is map your products on Winqual using the Product Feedback Mapping Tool , and nothing else. As long as your application is not trying to handle its own exceptions, you will get a WER report for your application crashes by default. When your exceptions bubble up the UEF in Windows, Windows Error Reporting is invoked. Depending on the type of crash (Application Hang, 32bit crash, 64bit crash, Buffer Overrun, etc), Windows Error Reporting will create your event type for you as well as handle creating a ��crash signature�� for the event used to report the event to Microsoft. Examples of these common crash eventtypes that are already built into WER are ��Crash32�� for an application crash on a 32bit machine, ��Crash64�� for an application crash on a 64bit machine, ��BEX�� for security related events such as Buffer Overrun, and Data Execute Prevention issues, and Safe CRT exceptions to name a few. The event type you are mimicking here (PCA2) is used by a service that handles product compatibility checking and reports on failed installation attempts. Mimicking an event will not help you because ��not all of these events (such as PCA2) are not exposed through Winqual and you need to successfully map on the valid signature type during a report. There is no reason to create a custom event for a crash or a hang because you get that event report for ��free��. You don��t have to do anything. Custom events are really for non-fatal events, and require a registration with Microsoft (a manual process today by contacting WER@microsoft.com ). I hope this helps answer your question. Kind regards, -Jason Tag: how can I count # of columns between two specific columns?
  - 12
    - VBA 6.5 New Version? I have loaded Office 2007 and the VBA about screen states version 6.5. Is there any documentation on recent changes to the VBA environment An older product which includes the VBA environment is not able to launch any VBA macros with Office 2007 installed. The upgrade to VBA 6.5 may be the problem and I would like to find some detailed information on new releases. Thank you, Greg Tag: how can I count # of columns between two specific columns?
  - 13
    - Updating column totals in sub forms This has to be simpler than I'm making it! Trying to maintain column ototals ni SubForm, and update them to the MainForm. Typical Northwind-type Orders-&-Details relationship: I extend row totals in the OrderDetails subform; and show OrderTotal (column total) in the SubForm's FormFooter. So far, so good. But when I change a quantity or price in the subform, I'd expect the subtotal to change automatically, no It does not; and I don't know whether I need to code a "Change" event myself, to modify that total whenever a single cell changes; and whether I must loop through the rows, or if there's a nifty group-operator in Access to do the columnTotal for me. (I'm a VB programmer, reluctantly roped into writing an Access form.) Finally, when I leave the Main Record (i.e., parent of the rows in the subForm), I want to store the updated suptotals in the Main/Parent record. (I know, I know, it's not kosher, but they pay me to do this stuff. -- Besides, it records what has been billed as it was billed; not as it's been updated since.) Many thanks, anyone who can provide a cogent reply. S Tag: how can I count # of columns between two specific columns?
  - 14
    - AutoExec will not execute properly your findings are all correct. In the AutoExec I have now added a small delay as shown Sub AutoExec() ' ' macro is run when AddIn is read in ' the only job is to set the entry in the menubar ' ' we have to wait until Word has done its job Application.OnTime When:=Now + TimeValue("00:00:03"), _ Name:="start_macro" End Sub ' AutoExec Note: the start_macro is as follows: Sub start_macro() show_warning = True Call initialise_program Call check_if_vpn(False) ' check if connection to server exists End Sub with this delay the entries in the menubar show up correctly. The sub initialise_program is identical in Excel and PowerPoint and executes correctly also on those PC's where the Wordmacro does not. Tag: how can I count # of columns between two specific columns?
  - 15
    - MS Office document and image writer print driver Thank you! Tag: how can I count # of columns between two specific columns?
  - 16
    - Fill word document with fields from Access form programmatically Hi - i have a form called 'formpo' that allows users to enter information that we use for purchase orders such as date, order value, supplier name, purchase description, etc etc. when the user has filled in the information on the form i would like them to be able to click a button on the form screen that will take the values from the form and insert them into a specific word document, and have the values entered into a specific place on the word template. There is a lot of stuff out there but its not the best and its mainly about mailmerging it which i dont want to have to do because its a right pain in the proverbial behind. Has anyone done this before Many thanks, Rhys. Tag: how can I count # of columns between two specific columns?