Search code examples
excelexcel-2007vba

error on getting the name of first sheet into the formula using macro excel 2007

I have tried with the code below in Excel 2007 and it generates a run time error 1004. I have tried with different ways to solve but could not. Need help in solving error because each of my first worksheet is named different.

   Dim shtName As String
   shtName = ActiveWorkbook.Worksheets(1).Name
   Worksheets(2).Range("F2").Formula = "=AVERAGEIFS(&shtName!E:E,&shtName!A:A,"">="" & A2,&shtName!A:A,""<"" & B2)"
Solution

  • You should detach your variables from strings by putting them outside the "" marks.

    So it is supposed to be

    Dim shtName As String
shtName = ActiveWorkbook.Worksheets(1).Name
Worksheets(2).Range("F2").Formula = "=AVERAGEIFS(" & shtName & "!E:E," & shtName & "!A:A,"">="" & A2," & shtName & "!A:A,""<"" & B2)"