PDA

View Full Version : formula/code cleanup



Gtrain
06-28-2010, 02:02 AM
any thoughts on how this can be optimized, i will be filling down until the last row, this row can potentially be 65536.

As forumla suggests i have a value in J being either T or L ( OR R)
if J is T or L the same ormula is appled, but if R then i have a number in I, depending on this number i apply a formula as per vba,
values are L2>=1000, 1000>L2>=700, 700>L2

Any thoughts on how i should improve it?

Thanks

G


Sub PageFormat()
Dim hData As Worksheet
Set hData = Worksheets("Height")
hData.Range("P2").Value = "=IF(J2=" & "T" & ",((0.006*(L2)^2)" & _
"+(2.2081*L2)+0.3359),IF(J2=" & "L" & ",((0.006*(L2)^2)+(2.2081*L2)" & _
"+0.3359),IF(L2>999,((0.0033*(L2)^2)+(2.301*L2)-0.0817),IF(L2>599," & _
"((0.001*(L2)^2)+(2.4163*L2)-0.2991),((0.0115*(L2)^2)+(2.752*L2)-0.4846)))))"
End Sub

Bob Phillips
06-28-2010, 02:51 AM
Sub PageFormat()
Dim hData As Worksheet
Dim lastRow As Long
Set hData = Worksheets("Height")
With hData

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("P2").Resize(lastRow - 1).Formula = _
"=IF(OR(J2=""T"",J2=""L""),((0.006*(L2)^2)+(2.2081*L2)+0.3359)," & _
"IF(L2>999,((0.0033*(L2)^2)+(2.301*L2)-0.0817)," & _
"IF(L2>599,((0.001*(L2)^2)+(2.4163*L2)-0.2991),((0.0115*(L2)^2)+(2.752*L2)-0.4846))))"
End With
End Sub