excel 2010 macro vba ActiveCell.Formula = produz uma célula vazia quando executado

Question

Esta fórmula macro (em um loop para colocar uma fórmula Vlookup para cada planilha recém-inserida) resulta em uma célula vazia quando executada:

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

a célula está totalmente em branco. Eu pesquisei nos blogs durante vários fins de semana e nenhum deles mencionou esse resultado.

Estou usando uma solução alternativa, "imprimindo" (abaixo) usando a mesma fórmula (adicionei algumas marcas de citação em alguns blogs) e ela cria as fórmulas em um arquivo de texto. Em seguida, cole as instruções case em outra macro e, quando ela é chamada para uma planilha específica, ela produz a fórmula correta na célula. Aqui está a declaração print que cria as declarações case no arquivo de texto:

    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

Copiando as instruções de caso resultantes em uma macro e produz a fórmula correta na célula:

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

É claro que minha pergunta é por que estou obtendo um resultado em branco? Existe uma configuração do Windows 7 ou do Centro de Confiabilidade do Excel que está impedindo a inserção da fórmula?

    

Answer 1

Altere para isto:

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

Sua fórmula atual não está computando ActiveWorkbook.Sheets (sheetname) .Columns ("" H: R "") porque está dentro da string, então tenta colocar isso na fórmula, mas o modelo de objeto VBA não é t acessível a partir de uma fórmula.