On 7 Jan, 11:32, 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))

try....

Sub test()
Dim r As integer
Worksheets("sheet1").Activate
r = Cells(Rows.Count, "N").End(xlUp).row
range("b2").formula = "sumproduct((N8:N" & r & "=0)*(N8:N" & r &
"<=1000))"
End Sub

rgds,
Davemac