使用这个SO答案，我一直在尝试使以下代码正常工作。
' inserting formulas into the userOutput.csv sheet
Dim wsUser As Worksheet: Set wsUser = Worksheets("userOutput.csv")
Dim agentEmailRange As Range: 'Set agentEmailRange = wsUser.Range(Cells(2, agentEmailColumn), Cells(propertyRows, agentEmailColumn))
' following line fails with runtime error 1004, method 'range' of object '_Worksheet' fialed.
Set agentEmailRange = wsUser.Range(Cells(2, agentEmailColumn), Cells(propertyRows, agentEmailColumn))
wsUser.Range("I1") = "Agent Email"
With agentEmailRange
.Value = "VLOOKUP(RC[-1], 'agentsOutput.csv'!R2C1:R" & agentRows & "C6 ,4, FALSE)"
End With
奇怪的是，它只能工作一次。但是，当我更改其中一个变量时，它开始失败。
如何动态地在需要的单元格中获取该公式？
试试这个：
Dim wsUser As Worksheet
Dim agentEmailRange As Range
Set wsUser = Worksheets("userOutput.csv")
With wsUser
Set agentEmailRange = .Range(.Cells(2, agentEmailColumn), .Cells(propertyRows, agentEmailColumn))
.Range("I1") = "Agent Email"
End With
agentEmailRange.Formula = "=VLOOKUP(RC[-1], 'agentsOutput.csv'!R2C1:R" & agentRows & "C6 ,4, FALSE)"
您应该完全限定自己的资格Cells，即指定Cells所属的工作表。请注意，我.Cells(..)改用了Cells(..)-在这种情况下，Excel知道，它Cells属于sheet wsUser。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句