vba runtime error 1004, trying to input a formula over a range of cells using for loop

0
votes

I am trying to run a vba script to automatically fill a formula in a range of cells. I am getting a runtime error 1004 with the for loop at the end of the code. I am trying to input cell ranges as variables in the for loop.

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

Any help would be appreciated.

2
excelvbaruntime-error
on which line do you get the error? – Scott Craner
BTW: You do not need the extra lines: dsac.Range("I2:I" & rn + 1).Formula = "=A2+273.15" and dsac.Range("J2:CZ" & rn + 1).Formula = "=$C$2*'Down Sweep Viscosity Shear-Rate'!C11^($B$2-1)" – Scott Craner
And the """ at the end of the loop formula should only be " – Scott Craner
You can also skip the loop: dsac.Range("J101:J195").Formula = "=(Ln(Down Sweep Viscosity Shear-Rate!C201 / J2)) / ((1 / ($I$2- $D$2)) - (1 / ($E$2 - $D$2)))" – Scott Craner

2 Answers

1
votes

To put my comments in an answer:

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)))"
0
votes
  • Avoid .select as this is a resource heavy method.
  • Don't use xlDown in your search for a last row as this will break if there is an empty row in the data set.
  • Formulas will slow the workbook down if there are a lot of calculations. If you don't need them, here is a solution for you.
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