Announcement

Collapse
No announcement yet.

Use Variables in Formulas Added via VBA Macro Code

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use Variables in Formulas Added via VBA Macro Code



    I need to open 32 files stored on our lan, select the current month tab, then sum a range of data to a summary sheet. I have searched this forum and gotten most of it done. What I am having a problem with is using variables I have set to hold the workbook name and tab name in a formula. The variables are being set from cells on the summary sheet which contains the path for the file and the file name (these change 32 times)
    My code so far"
    Code:
    Sub Focus_Summary2()
    Dim WBName
    Dim Month
    Dim MPath
    Dim FOpen
    
    WBName = ActiveWorkbook.Name       'Name of Workbook to contain the Data Retrieved
    MPath = Range("D3").Value & "\"    'Path to File
    FOpen = Range("E3").Value          'Name of File to Open
    Month = Range("F1").Value          'Tab to use for data retrievel
    '********* Change Directory and Open workbook *******************************
        ChDir MPath
         Workbooks.Open Filename:= _
            (MPath & FOpen)
    '******************************************************************************
        Windows(WBName).Activate
        'Formula to sum data
        ActiveCell.FormulaR1C1 = _
            "=SUM('[Kansas City_Focus_Account Tracker.xls]" & Month & "'!R14C4:R53C8)"
       Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveCell.Offset(1, 0).Range("A1").Select
    Windows(FOpen).Close
    End Sub
    I want to replace the workbook name [Kansas City_Focus_Account Tracker.xls] with the variable FOpen
    " & Month & " is the variable used to hold the sheet name.
    I tried using the following code
    Code:
        ActiveCell.FormulaR1C1 = _
            "=SUM('[" & FOpen & " & Month & " '!R14C4:R53C8)"
    but it produces the following formula =SUM('C:\[Kansas City_Focus_Account Tracker.xlsAug]Kansas City_Focus_Account Track'!$D$14:$H$53)
    I need it to be =SUM('[Kansas City_Focus_Account Tracker.xls]Aug'!$D$14:$H$53)

    I have tried several variations of this with no luck

    Thanks in advance for your help
    Last edited by TJE; August 30th, 2006, 22:17.

  • #2
    Re: Use Variables in VBA Formula

    You VBA snippet:
    Code:
    ActiveCell.FormulaR1C1 = _ "=SUM('[" & FOpen & " & Month & " '!R14C4:R53C8)"

    has unbalanced quotes, so is illegal. Is this really what you have?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

    Comment


    • #3
      Re: Use Variables in VBA Formula

      Originally posted by TJE
      but it produces the following formula =SUM('C:\[Kansas City_Focus_Account Tracker.xlsAug]Kansas City_Focus_Account Track'!$D$14:$H$53)
      I need it to be
      =SUM('C:\[Kansas City_Focus_Account Tracker.xlsAug]Kansas City_Focus_Account Track'!$D$14:$H$53)
      But there's no difference btwn what it produces & what you say you want (above)

      Edit: ... bah! you edited your post!
      Last edited by Will Riley; August 30th, 2006, 22:23.
      Kind Regards, Will Riley

      LinkedIn: Will Riley

      Comment


      • #4
        Re: Use Variables in VBA Formula

        Sorry, I didn't realize I had copied the wrong formula result until I had submitted it.

        Comment


        • #5
          Re: Use Variables in VBA Formula

          try


          ActiveCell.FormulaR1C1 = _
          "=SUM('[" & fopen & "]" & month & "'!R14C4:R53C8)"
          Kind Regards, Will Riley

          LinkedIn: Will Riley

          Comment


          • #6


            Re: Use Variables in VBA Formula

            Perfect! Thanks : D

            Comment

            Working...
            X