ExcelBanter - SumProduct for select to the last cell that contains data???

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SumProduct for select to the last cell that contains data??? (https://www.excelbanter.com/excel-worksheet-functions/121378-sumproduct-select-last-cell-contains-data.html)

mniccole

SumProduct for select to the last cell that contains data???

Hi:
How would I change the formula below to go only the last row that
contains data instead of setting a static range?
Please help
Thanks

Worksheets("Menu").Range("C18").Formula =
"=SUMPRODUCT((BillingHoldDetails!M2:M3000)*(Billin gHoldDetails!$A$2:$A$3000=""AP
BILLING""))"

Bob Phillips

SumProduct for select to the last cell that contains data???

Worksheets("Menu").Range("C18").Formula = _
"=SUMIF(BillingHoldDetails!$A:$A,""AP
BILLING"",BillingHoldDetails!$M:$M)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"mniccole" wrote in message
ps.com...
Hi:
How would I change the formula below to go only the last row that
contains data instead of setting a static range?
Please help
Thanks

Worksheets("Menu").Range("C18").Formula =
"=SUMPRODUCT((BillingHoldDetails!M2:M3000)*(Billin gHoldDetails!$A$2:$A$3000=""AP
BILLING""))"

mniccole

SumProduct for select to the last cell that contains data???

Thanks Bob...
Works perfect.

Bob Phillips wrote:
Worksheets("Menu").Range("C18").Formula = _
"=SUMIF(BillingHoldDetails!$A:$A,""AP
BILLING"",BillingHoldDetails!$M:$M)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"mniccole" wrote in message
ps.com...
Hi:
How would I change the formula below to go only the last row that
contains data instead of setting a static range?
Please help
Thanks

Worksheets("Menu").Range("C18").Formula =
"=SUMPRODUCT((BillingHoldDetails!M2:M3000)*(Billin gHoldDetails!$A$2:$A$3000=""AP
BILLING""))"

mniccole

Loop QUESTION to end of data range...

HI:
Can you tell me how to loop to the end of the data range without this
being static?
Please

numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
Range("A2").EntireColumn.Select

For x = 1 To 3000

Thanks so much

Bob Phillips wrote:
Worksheets("Menu").Range("C18").Formula = _
"=SUMIF(BillingHoldDetails!$A:$A,""AP
BILLING"",BillingHoldDetails!$M:$M)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"mniccole" wrote in message
ps.com...
Hi:
How would I change the formula below to go only the last row that
contains data instead of setting a static range?
Please help
Thanks

Worksheets("Menu").Range("C18").Formula =
"=SUMPRODUCT((BillingHoldDetails!M2:M3000)*(Billin gHoldDetails!$A$2:$A$3000=""AP
BILLING""))"

Roger Govier

Loop QUESTION to end of data range...

see response to your later posting in a new thread.

--
Regards

Roger Govier

"mniccole" wrote in message
oups.com...
HI:
Can you tell me how to loop to the end of the data range without this
being static?
Please

numrows = Range("A2", Range("A2").End(xlDown)).Rows.Count
Range("A2").EntireColumn.Select

For x = 1 To 3000

Thanks so much

Bob Phillips wrote:
Worksheets("Menu").Range("C18").Formula = _
"=SUMIF(BillingHoldDetails!$A:$A,""AP
BILLING"",BillingHoldDetails!$M:$M)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"mniccole" wrote in message
ps.com...
Hi:
How would I change the formula below to go only the last row that
contains data instead of setting a static range?
Please help
Thanks

Worksheets("Menu").Range("C18").Formula =
"=SUMPRODUCT((BillingHoldDetails!M2:M3000)*(Billin gHoldDetails!$A$2:$A$3000=""AP
BILLING""))"

All times are GMT +1. The time now is 08:48 PM.