Counting cells using EVALUATE

[BB Ivan]

I'm trying to calculate and return a count based on a range and am having
trouble getting it right. I am counting retail store numbers that may be
typed in the range. I then use the count in other place in my macro.

Non-working code:
funcRange = "ActiveSheet.Cells(""DX1:IV1"")"
funcName = "=COUNTA"
funcNameRange = funcName & "(" & funcRange & ")"
mStoreCount = Evaluate(funcNameRange)

 

[BB Ivan]

Sorry. I figured it out. Thanks!

funcRange = "'" & ActiveSheet.Name & "'!DX1:IV1"
funcName = "=COUNTA"
funcNameRange = funcRange & "(" & funcName & ")"
mStrCount = Evaluate(funcNameRange )

 

[Gary''s Student]

Sub dural()
funcRange = "DX1:IV1"
funcName = "=COUNTA"
funcNameRange = funcName & "(" & funcRange & ")"
MsgBox (funcNameRange)
mStoreCount = Evaluate(funcNameRange)
MsgBox (mStoreCount)
End Sub


If you use COUNTA directly in VBA, the argument must be a Range. If you use
COUNTA within EVALUATE, the whole thing is just a String.

 

[BB Ivan]

Thanks so much for your help!

Sub dural()
funcRange = "DX1:IV1"
funcName = "=COUNTA"
funcNameRange = funcName & "(" & funcRange & ")"
MsgBox (funcNameRange)
mStoreCount = Evaluate(funcNameRange)
MsgBox (mStoreCount)
End Sub


If you use COUNTA directly in VBA, the argument must be a Range. If you use
COUNTA within EVALUATE, the whole thing is just a String.

 

[JE McGimpsey]

Alternatively:

mStrCount = Application.CountA(ActiveSheet.Range("DX1:IV1"))

 

[BB Ivan]

Better and simpler! Thanks

Alternatively:

mStrCount = Application.CountA(ActiveSheet.Range("DX1:IV1"))