Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 " |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
Subtract cell formula from existing cell formula | Excel Programming | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |