Hi,

Try this

Dim LASTCELLADDRESS As Long
LASTCELLADDRESS = Cells(Cells.Rows.Count, "N").End(xlUp).Row
Range("b2").Formula = "=sumproduct((N8:N" & _
LASTCELLADDRESS & "=0)*(N8:N" & LASTCELLADDRESS & "<=1000))"


Mike

"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))