Results 1 to 6 of 6

20050427, 18:26 #1
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Variables in formulas (office 2000)
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, Applicationdefined or objectdefined error.
Thank YouKevin

20050427, 18:33 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Variables in formulas (office 2000)
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 megaformula.
Steve

20050427, 18:49 #3
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Variables in formulas (office 2000)
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)?
ThanksKevin

20050427, 18:51 #4
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Variables in formulas (office 2000)
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 YouKevin

20050427, 18:57 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Variables in formulas (office 2000)
Something like this:
<pre>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
</pre>
Legare Coleman

20050427, 20:01 #6
 Join Date
 Jan 2001
 Location
 New England (Connecticut)
 Posts
 200
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Variables in formulas (office 2000)
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 = strFormulaKevin