Error Application Defined or Object Defined

[Priyanka]

Hi

Was trying to write a macro to insert a formula in a range of cells. The
last cell is calculated using the function Lastrow. However when i run it, I
get the following error:
"Application-defined or Object-defined error"
This is the macro.

Sub Max_range_calc()

' Keyboard Shortcut: Ctrl+Shift+M
Dim i, k As Integer
k = LastRow(7)
For i = 1 To k
Cells(i, 7).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-1]"
Next i
End Sub

Function LastRow(C)
R = 10000 'set R to be greater than your maxim row number
Do
If (Cells(R, C)) <> "" Then Exit Do Else R = R - 1
Loop
LastRow = R
End Function

Please Help!!!
Thanks a bunch

Priyanka

 

[Barb Reinhardt]

I'd do it this way.

Sub Max_range_calc()

' Keyboard Shortcut: Ctrl+Shift+M
Dim i, k As Integer
Dim aWS As Worksheet
Dim myRange As Range
Dim lrow as long

Set aWS = ActiveSheet
lrow = aWS.Cells(aWS.Rows.Count, 7).End(xlUp).Row
Set myRange = aWS.Cells(1, 7).Resize(lrow, 1)
myRange.FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-1]"

End Sub

HTH,
Barb Reinhardt

 

[Nigel]

try using

Cells(Rows.Count,7).End(xlUp).Row

to get your last row the method you are using is not efficient so try.....

Dim k As Long
k = Cells(Rows.Count, 7).End(xlUp).Row
Range(Cells(1, 7), Cells(k, 7)).FormulaR1C1 = "=(RC[-2]-RC[-3])/RC[-1]"