Thread
:
using =COUNTIF() in a variable range on a different worksheet?
View Single Post
#
4
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
Posts: 10,124
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.
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett