Object visibility?

[CDTech]

Hello all.

Can anyone tell me why this works
[vba]Worksheets(sThisSample).Activate
Application.Range("A1").Activate

'Count returns long data type, cast to integer.
iAnswer = CInt(WorksheetFunction.Count(Worksheets(sThisSample).Range("A2:A8000")))
[/vba]

yet this doesn't

[vba]Worksheets(sThisSample).Activate
Application.Range("A1").Activate

'Count returns long data type, cast to integer.
iAnswer = CInt(WorksheetFunction.Count("A2:A8000"))
[/vba]

I assume since worksheet sThisSample is in scope, the count function will use the range A2:A8000 on that worksheet. It works fine in an earlier section of code where the starting sheet is active.

[mbarron]

With this:
[VBA]iAnswer = CInt(WorksheetFunction.Count("A2:A8000"))[/VBA]
you are trint to count a string ("A2:A8000"), not the range "A2:A8000"

use:
[VBA]iAnswer = CInt(WorksheetFunction.Count(Range("A2:A8000")))[/VBA]

[CDTech]

With this:
[vba]iAnswer = CInt(WorksheetFunction.Count("A2:A8000"))[/vba]
you are trying to count a string ("A2:A8000"), not the range "A2:A8000"

use:
[vba]iAnswer = CInt(WorksheetFunction.Count(Range("A2:A8000")))[/vba]

All those emoticons and there's not a facepalm.

Okay, I fixed that dumb error yet I still have to specify the worksheet when using Worksheetfunction.Count. I guess it doesn't pay attention to which worksheet is in scope and expects it to be explicit in the call even though there's no error message if you don't spec it.

Thanks for the help.

[mbarron]

Your first line of code will evaluate to 0 since there a 0 numbers in the string.

How about this one?


Type

:OMG2:

[CDTech]

Your first line of code will evaluate to 0 since there a 0 numbers in the string.

How about this one?


Type

:OMG2:

Um, yeah. Symptomatic of my inability to successfully proofread my stuff.

As for my first line of code, since the function expects a range object, it didn't occur to me I had to explicitly pass it a range object. I assumed it would resolve the range address as the range object I wanted to process.

I STILL need a book that discusses these kinds of subtle nuances that keep tripping me up. Or accept that I can't program my way out of a wet paper sack and should just give up.