using =COUNTIF() in a variable range on a different worksheet?
 

Help please.

I just got an nice answer on how to use the ".Formula()" function
programmatically but I forgot one detail: the range has to be
variable:

'ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D36000,""<N/A"")"

So, imagine that the range "D2:D36000" is dynamic. I've been trying
to substitute the value of "D36000" using "&d_end" but no success.
Something is telling me I should use a "range" type variable but I'm
too newbie I think.

Thanks.

using =COUNTIF() in a variable range on a different worksheet?
 

try something like:

lastrow=sheets("wsa").cells(rows.count,"d").end(xl up).row
ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D" & lastrow & ",""<N/A"")"



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"circuit_breaker" wrote in message
...
Help please.

I just got an nice answer on how to use the ".Formula()" function
programmatically but I forgot one detail: the range has to be
variable:

'ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D36000,""<N/A"")"

So, imagine that the range "D2:D36000" is dynamic. I've been trying
to substitute the value of "D36000" using "&d_end" but no success.
Something is telling me I should use a "range" type variable but I'm
too newbie I think.

Thanks.

using =COUNTIF() in a variable range on a different worksheet?
 

On Jul 6, 11:11*am, "Don Guillett" wrote:
try something like:

lastrow=sheets("wsa").cells(rows.count,"d").end(xl up).row
ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!

D2:D" & lastrow & ",""<N/A"")"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"circuit_breaker" wrote in message

...



Help please.

I just got an nice answer on how to use the ".Formula()" function
programmatically but I forgot one detail: the range has to be
variable:

'ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D36000,""<N/A"")"

So, imagine that the range "D2:D36000" is dynamic. *I've been trying
to substitute the value of "D36000" using "&d_end" but no success.
Something is telling me I should use a "range" type variable but I'm
too newbie I think.

Thanks.- Hide quoted text -

- Show quoted text -

Brilliant. It worked. However, I changed your formula for:

LastCellInColumn = ActiveWorkbook.Sheets("WSA").UsedRange.Rows.Count

in order to pick the very last cell used in that column.

Thanks again.

using =COUNTIF() in a variable range on a different worksheet?
 

Sending you a workbook showing that if you want to get the last row in col
D.

Sub usedrngelastrow()
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox "Using xlup col d is row " & Cells(Rows.Count, "d").End(xlUp).Row
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"circuit_breaker" wrote in message
...
On Jul 6, 11:11 am, "Don Guillett" wrote:
try something like:

lastrow=sheets("wsa").cells(rows.count,"d").end(xl up).row
ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!

D2:D" & lastrow & ",""<N/A"")"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"circuit_breaker"
wrote in message

...



Help please.

I just got an nice answer on how to use the ".Formula()" function
programmatically but I forgot one detail: the range has to be
variable:

'ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D36000,""<N/A"")"

So, imagine that the range "D2:D36000" is dynamic. I've been trying
to substitute the value of "D36000" using "&d_end" but no success.
Something is telling me I should use a "range" type variable but I'm
too newbie I think.

Thanks.- Hide quoted text -

- Show quoted text -

Brilliant. It worked. However, I changed your formula for:

LastCellInColumn = ActiveWorkbook.Sheets("WSA").UsedRange.Rows.Count

in order to pick the very last cell used in that column.

Thanks again.

using =COUNTIF() in a variable range on a different worksheet?
 

To find the very last row of the sheet

lastrow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

MsgBox "Real last row of spreadsheet is " & lastrow

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Sending you a workbook showing that if you want to get the last row in col
D.

Sub usedrngelastrow()
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox "Using xlup col d is row " & Cells(Rows.Count, "d").End(xlUp).Row
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"circuit_breaker" wrote in message
...
On Jul 6, 11:11 am, "Don Guillett" wrote:
try something like:

lastrow=sheets("wsa").cells(rows.count,"d").end(xl up).row
ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!

D2:D" & lastrow & ",""<N/A"")"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"circuit_breaker"
wrote in message

...



Help please.

I just got an nice answer on how to use the ".Formula()" function
programmatically but I forgot one detail: the range has to be
variable:

'ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula ="=COUNTIF('WSA'!
D2:D36000,""<N/A"")"

So, imagine that the range "D2:D36000" is dynamic. I've been trying
to substitute the value of "D36000" using "&d_end" but no success.
Something is telling me I should use a "range" type variable but I'm
too newbie I think.

Thanks.- Hide quoted text -

- Show quoted text -

Brilliant. It worked. However, I changed your formula for:

LastCellInColumn = ActiveWorkbook.Sheets("WSA").UsedRange.Rows.Count

in order to pick the very last cell used in that column.

Thanks again.