Last cell in new formula
 

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

Last cell in new formula
 

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

Last cell in new formula
 

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

Last cell in new formula
 

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

Last cell in new formula
 

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

"

Last cell in new formula
 

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

--
Jacob


"wynand" wrote:

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

"

Last cell in new formula
 

THANKS AGAIN!

"Jacob Skaria" wrote:

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

--
Jacob


"wynand" wrote:

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

"