Try

Dim lngLastRow As Long
lngLastRow = Worksheets("sheet1").Cells(Rows.Count, "N").End(xlUp).Row

Range("b2").Formula = _
"=Sumproduct((N8:N" & lngLastRow & "=0)*(N8:N" & lngLastRow & "<=1000))"

--
Jacob


"wynand" wrote:

Can anyone please help?

The code:
Sub test()
Dim x As Range
Worksheets("sheet1").Activate
Set x = Cells(Rows.Count, "N").End(xlUp)
MsgBox x.Address
End Sub

I would like to find the last cell in column N and instead of the result
being displayed in A msgbox, I would like to use the cell address in a
formula after the code above e.g.:
range("b2").formula = _
"sumproduct((N8:LASTCELLADDRESS=0)*(N8:LASTCELLAD DRESS<=1000))