Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    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, Application-defined or object-defined error.

    Thank You
    Kevin

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 mega-formula.

    Steve

  3. #3
    3 Star Lounger
    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)?

    Thanks
    Kevin

  4. #4
    3 Star Lounger
    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 You
    Kevin

  5. #5
    Uranium Lounger
    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

  6. #6
    3 Star Lounger
    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 = strFormula
    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •