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

[circuit_breaker]

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'!
D236000,""<>N/A"")"

So, imagine that the range "D236000" 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.

 

[Don Guillett]

try something like:

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

D2" & lastrow & ",""<>N/A"")"

 

[circuit_breaker]

try something like:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software









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

 

[Don Guillett]

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
(e-mail address removed)

try something like:

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)"circuit_breaker" <[email protected]>
wrote in message









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

 

[Don Guillett]

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