vba运行时错误1004，试图使用for循环在一系列单元格上输入公式

Question

我正在尝试运行一个vba脚本，以便在一系列单元格中自动填充公式。我在代码末尾得到一个带有for循环的运行时错误1004。我试图在for循环中输入单元格范围作为变量。

Dim dsac As Worksheet
Dim dsvs As Worksheet
Dim tcrng As Range
Dim tkrng As Range
Dim arng As Range
Dim arng2 As Range
Dim arng3 As Range
Dim arng4 As Range
Dim rn As Integer
Dim i As Integer
Set dsac = Worksheets("DownSweep Alpha Calculation")
Set dsvs = Worksheets("Down Sweep Viscosity Shear-Rate")
Set tcrng = dsac.Range(dsac.Range("A2"), dsac.Range("A2").End(xlDown))
rn = tcrng.Rows.Count
dsac.Range("I2").Formula = "=A2+273.15"
dsac.Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & rn + 1), Type:=xlFillDefault
dsac.Range("J2").Formula = "=$C$2*'Down Sweep Viscosity Shear-Rate'!C11^($B$2-1)"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:CZ2"), Type:=xlFillDefault
Range("J2:CZ2").Select
Selection.AutoFill Destination:=Range("J2:CZ" & rn + 1), Type:=xlFillDefault
i = 1
Set arng = dsac.Range("J101")
Set arng2 = dsvs.Range("C201")
Set arng3 = dsac.Range("J2")
Set arng4 = dsac.Range("I2")

For i = 1 To 95
arng.Formula = "= (Ln(" & arng2 & " / " & arng3 & ")) / ((1 / (" & arng4 & "- $D$2)) - (1 / ($E$2 - $D$2)))"""
arng = arng.Offset(0, 1)
arng2 = arng2.Offset(0, 1)
arng3 = arng3.Offset(0, 1)
Next i

如有任何帮助，我们将不胜感激。

Answer 1

把我的评论放在一个答案中：

Dim dsac As Worksheet
Set dsac = Worksheets("DownSweep Alpha Calculation")
Dim dsvs As Worksheet
Set dsvs = Worksheets("Down Sweep Viscosity Shear-Rate")
Dim rn As Long
rn = dsac.Cells(dsac.Rows.Count, 1).End(xlUp).Row
dsac.Range("I2:I" & rn).Formula = "=A2+273.15"
dsac.Range("J2:CZ" & rn).Formula = "=$C$2*'Down Sweep Viscosity Shear-Rate'!C11^($B$2-1)"
dsac.Range("J101:J195").Formula = "=(Ln(Down Sweep Viscosity Shear-Rate!C201 / J2)) / ((1 / ($I$2- $D$2)) - (1 / ($E$2 - $D$2)))"

Answer 2

• 避免.select，因为这是一种资源密集的方法
• 不要在搜索最后一行时使用xlDown，因为如果数据集中有空行，这会中断
• 如果计算量很大，公式会降低工作簿的速度。如果你不需要它们，这里有一个适合你的解决方案

---

Sub test()
Dim dsac As Worksheet: Set dsac = Worksheets("DownSweep Alpha Calculation")
Dim dsvs As Worksheet: Set dsvs = Worksheets("Down Sweep Viscosity Shear-Rate")
Dim i As Integer
Dim lrow As Long
lrow = dsac.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
dsac.Cells(i, 9).Value = dsac.Cells(2, 1) * 273.15
Next i
For i = 10 To 104 '104 is column CZ 
dsac.Cells(2, i).Value = dsac.Cells(2, 3) * dsvs.Cells(11, i) ^ dsvs.Cells(2, 2) - 1
Next i
For i = 10 To 85 'you had 95, but this starts at 10 so reduced to 85
dsac.Cells(101, i).Value = WorksheetFunction.Ln((dsvs.Cells(201, i - 7) / dsac.Cells(2, 10)) / ((1 / (dsac.Cells(2, 9) - dsac.Cells(2, 4)))) - ((1 / (dsac.Cells(2, 5) - dsac.Cells(2, 4)))))
Next i
End Sub