Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This macro formula (in a loop to place a Vlookup formula for each newly inserted worksheet) results in an empty cell when run:

   ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", ActiveWorkbook.Sheets(sheetname).Columns(""H:R""), 7, True)"

the cell is totally blank. I've searched the blogs over several weekends and none have mentioned this result.

I'm using a workaround, "printing" (below) using the very same formula (added some quote marks per some blogs) and it properly creates the formulas in a text file. I then paste the case statements in another macro and when it is called for a particular worksheet it produces the correct formula in the cell just fine. Here is the print statement that creates the case statements in the text file:

    ffile = FreeFile()
    Open MyFile For Output As ffile
    Print #ffile, "Sub SelectTabsFormulas()"
    'create Option1 Vlookups
    Print #ffile, "    Select Case sheetname"
    For FNum = LBound(MyOthExpFiles) To UBound(MyOthExpFiles)
        sheetname = Trim(Mid(MyOthExpFiles(FNum), 6, 4))
        Print #ffile, "    Case " & sheetname & ""
        Print #ffile, "        ActiveCell.Formula = ""=VLookup(""""Total Other Operating Expenses"""", '" & sheetname & "'!H:R, 7, True)"""
        FCnt = FCnt + 1
    Next FNum
    Print #ffile, "    Case Else"
    Print #ffile, "        ActiveCell.Value = """
    Print #ffile, "    End Select"
    Print #ffile, "    "
    Print #ffile, "    Return"
    Print #ffile, "End Sub"
    Close #ffile

Copying the resulting case statements into a macro and it produces the correct formula in the cell:

Sub SelectTabsFormulas()
  Select Case sheetname
    Case 112
      ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", '112'!H:R, 7, True)"
    Case 114
      ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", '114'!H:R, 7, True)"
    Case 9112
      ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", '9112'!H:R, 7, True)"
    Case Else
      ActiveCell.Value = ""
  End Select

  Return
End Sub

Of course my question is why am I getting a blank result? Is there a Windows 7 or Excel Trust Center setting that is preventing the formula insertion?

share|improve this question
    
I've fixed some of your formatting, but your first formula doesn't look right. Please make sure it is; kinda confusing right now. – Lance Roberts Sep 5 '11 at 18:48
    
thanks Lance. The formula did not paste right the first time, or was changed somehow when I hit Submit. Here it is again:ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", ActiveWorkbook.Sheets(sheetname).Columns(""H:R""), 7, True) " – Mel Dreogemeier Sep 5 '11 at 20:01

Change it to this:

ActiveCell.Formula = "=VLookup(""Total Other Operating Expenses"", " & _
                     sheetname & "!H:R, 7, True)"

Your current formula isn't computing ActiveWorkbook.Sheets(sheetname).Columns(""H:R"") because it's inside the string, so it tries to put that in the formula, but the VBA object model isn't accessible from a formula.

share|improve this answer
    
Lance... Your change worked perfectly! if I had run into you earlier you would have saved me struggling over the last three weekends, endless blogging and trying numerous examples, then finally writing 9 extra macros to implement a workaround. Thank you so much! – Mel Dreogemeier Sep 6 '11 at 22:02
    
Great to hear. If it solved your problem you can click on the checkmark below the voting arrows to accept the answer. – Lance Roberts Sep 7 '11 at 1:33
    
I don't see any voting arrows so I can indicate a positive answer. I'll check again tomorrow to see your directions on how to vote. (looked in FAQ, etc...) – Mel Dreogemeier Sep 7 '11 at 3:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .