Excel 2010 VBA макрос ActiveCell.Формула = выдает пустую ячейку при запуске

Question

Эта макро формула (в цикле для размещения формулы Vlookup для каждого вновь вставленного рабочего листа) приводит к пустой ячейке при запуске:

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

клетка абсолютно пуста Я искал блоги в течение нескольких выходных, и никто не упомянул этот результат.

Я использую обходной путь, "печать" (ниже), используя ту же формулу (добавил несколько кавычек для некоторых блогов), и он правильно создает формулы в текстовом файле. Затем я вставляю операторы case в другой макрос, и когда он вызывается для определенного рабочего листа, он просто создает правильную формулу в ячейке. Вот оператор print, который создает операторы case в текстовом файле:

    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

Копируем результирующие операторы case в макрос и получаем правильную формулу в ячейке:

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

Конечно, мой вопрос: почему я получаю пустой результат? Есть ли в Windows 7 или Excel Trust Center параметр, который запрещает вставку формулы?

Answer 1

Измените это на это:

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

Ваша текущая формула не вычисляет ActiveWorkbook.Листы (SheetName).Столбцы ("" H:R ""), потому что они находятся внутри строки, поэтому он пытается вставить это в формулу, но объектная модель VBA не доступна из формулы.