Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.