Last cell in new formula

[wynand]

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:LASTCELLADDRESS<=1000))

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[Dave Mac]

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:LASTCELLADDRESS<=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

 

[wynand]

THANKS GUYS, BOTH WORK PERFECT!

If I would like the "b2" result to be always be under the last cell e.g.
N16, what would you suggest

"

 

[Jacob Skaria]

Thanks for the feedback. Try

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

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

 

[wynand]

THANKS AGAIN!

Thanks for the feedback. Try

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

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