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