我已经尝试了以下代码在Excel 2007中,它生成了运行时间错误1004。我尝试了不同的方法来解决但不能。需要帮助解决错误,因为我的每个工作表都被命名为不同。
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)"
您需要将shtName
变量的值插入公式中,而不是变量名称本身:
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)"
(通常是一个好主意
您应该将变量从字符串中分离出来,将它们放在"标记外。
所以应该是
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)"