Variables in formulas (office 2000)

Topic

New Reply

Is it possible to pas a variable to a formula with in VBA? Example i am trying to add a specific cell in all the spreadsheets in a workbook to a summary page. I am trying to duplicate
( ActiveCell.FormulaR1C1 = _
“=’F700001 CLIN 02AC 728′!RC+’F700001 CLIN 02AA 726′!RC+’F700001 CLIN 728′!RC+’F700001 CLIN 726′!RC+’F700001 CLIN 0001 999′!RC+’F700001 CLIN 999′!RC”)

using a variable that has trapped the spreedsheet names like
(“ActiveCell.FormulaR1C1 = “= & wkshtnames(i – 1) & !RC+ & wkshtnames(i – 2) & !RC…”)

the variable wkshtnames holds all of the names of the spreedsheets in an array. No ,atter what I try I get a run time 1004 error, Application-defined or object-defined error.

Thank You

Reply | Quote

Replies

The variables should not be in the quotes Something like:

ActiveCell.FormulaR1C1 = “= ” & wkshtnames(i – 1) & “!RC+” & wkshtnames(i – 2) & “!RC…”

Though it might be better adding them in a loop into a string variable and then putting that in the formulaR1C1 instead of a mega-formula.

Steve

Reply | Quote

Steve, thanks for the reply, please explain the loop in more detail since I just realized that at any particular time I run this I don’t know how many spreadsheets there are going to be so I need to build the formula on the fly.
Is it even possible to build a formula on the fly with variables. (i.e. the number of sheets, the names of the sheets and the number of joins to add them all together)?

Thanks

Reply | Quote

Steve, managed to figure out the syntax as follows:
ActiveCell.FormulaR1C1 = “='” & wkshtnames(i – 1) & “‘!RC+'” & wkshtnames(i – 2) & “‘!RC+'” _
& wkshtnames(i – 3) & “‘!RC+'” & wkshtnames(i – 4) & “‘!RC+'” _
& wkshtnames(i – 5) & “‘!RC+'” & wkshtnames(i – 6) & “‘!RC”

The problem as said in the previous… This assumes that there will always be 6 sheets to be added together. I need to make the number of joins a variable as well. Not sure how to do this.

Thank You

Reply | Quote

Something like this:

Dim strFormula as string
Dim I As Long
    strformula=ActiveCell.FormulaR1C1 = "= " & wkshtnames(1) & "!RC
    For I =  to lNumWkSheets
        strFormula = strFormula & "+" & wkshtnames(i) & "!RC"
    Next I
    ActiveCell.FormulaR1C1 = strFormula

Reply | Quote

legare,
Thank You for your help. The following is the code that I actually ended with through your help. Thanks again

Dim ws As Double
ws = Worksheets.Count
MsgBox “The total number of worsheets in this workbokk is ” & ws – 2
ws = ws – 2
‘Sub ARRAY_sheetnames()
Dim wksht As Worksheet
Dim I As Long
Dim wkshtnames() ‘This is an array definition
I = 0
For Each wksht In ActiveWorkbook.Worksheets
I = I + 1
ReDim Preserve wkshtnames(1 To I)
wkshtnames(I) = wksht.Name
Next wksht

For I = 1 To ws

‘MsgBox wkshtnames(i)
Sheets(“tblTarData”).Select
Range(“N1”).Offset(I, 0).Value = wkshtnames(I)
Next I
‘End Sub

Sheets(“TAR Summary”).Select
Range(“C27″).Select
MsgBox ” = ” & wkshtnames(I – 1) & ” and ” & wkshtnames(I – 2)

Dim strFormula As String
Dim Z As Long
Z = 1
strFormula = “='” & wkshtnames(I – 1) & “‘!RC”
For Z = 2 To ws ‘lNumWkSheets
strFormula = strFormula & “+'” & wkshtnames(I – Z) & “‘!RC”
Next Z
ActiveCell.FormulaR1C1 = strFormula

Reply | Quote