Counting cells using EVALUATE
 

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)

Counting cells using EVALUATE
 

Sorry. I figured it out. Thanks!

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


"BB Ivan" wrote:

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)

Counting cells using EVALUATE
 

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.
--
Gary''s Student - gsnu200829


"BB Ivan" wrote:

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)

Counting cells using EVALUATE
 

Thanks so much for your help!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200829


"BB Ivan" wrote:

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)

Counting cells using EVALUATE
 

Alternatively:

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

In article ,
BB Ivan wrote:

Sorry. I figured it out. Thanks!

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

Counting cells using EVALUATE
 

Better and simpler! Thanks

"JE McGimpsey" wrote:

Alternatively:

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

In article ,
BB Ivan wrote:

Sorry. I figured it out. Thanks!

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