Last cell in new formula

  • Thread starter Thread starter wynand
  • Start date Start date
W

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))
 
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
 
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))"
 
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))
Click to expand...

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

"
 
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))"
 
THANKS AGAIN!

Jacob Skaria said:
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))"
Click to expand...
 
Back
Top