Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Use Variables in Formulas Added via VBA Macro Code

  1. #1
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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"
    VB:
    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
    VB:
    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 at 23:17.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd March 2006
    Location
    London
    Posts
    988

    Re: Use Variables in VBA Formula

    You VBA snippet:
    VB:
    [LEFT]ActiveCell.FormulaR1C1 = _ 
    "=SUM('[" & FOpen & " & Month & " [COLOR=darkgreen] '!R14C4:R53C8)"[/COLOR]
    [COLOR=#006400][/COLOR][/LEFT] 
    
    

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Use Variables in VBA Formula

    Quote 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 at 23:23.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  4. #4
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    Re: Use Variables in VBA Formula

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Use Variables in VBA Formula

    try

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

    Web Presence:
    LinkedIn: Will Riley

  6. #6
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    Re: Use Variables in VBA Formula

    Perfect! Thanks : D

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Use Variable Inside Formula Added By Macro Code
    By excelfinder in forum EXCEL HELP
    Replies: 12
    Last Post: June 4th, 2008, 04:50
  2. Replies: 2
    Last Post: April 15th, 2008, 01:54
  3. Run Macro Code When New Sheet Added
    By fahadkhowaja in forum EXCEL HELP
    Replies: 7
    Last Post: April 2nd, 2008, 20:15
  4. Formulas Added From Macro Code Don't Update
    By MathForMarines in forum EXCEL HELP
    Replies: 3
    Last Post: March 12th, 2008, 07:59
  5. Variable in Formula Added via Macro Code
    By Necromis in forum EXCEL HELP
    Replies: 4
    Last Post: September 6th, 2006, 05:48

Bookmarks

Posting Permissions

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