Counting cells using EVALUATE

B

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)
 
B

BB Ivan

Sorry. I figured it out. Thanks!

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

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

BB Ivan

Thanks so much for your help!

Gary''s Student said:
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.
Click to expand...
 
Top