| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 ) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =EVALUATE("{ to work in a range of cells | Excel Worksheet Functions | |||
| How do I evaluate a row of cells for a character in each cell? | Excel Worksheet Functions | |||
| Function evaluate multiple cells and return 1st one w/a value | Excel Discussion (Misc queries) | |||
| evaluate two cells and calculate based on criteria | Excel Worksheet Functions | |||
| Using AND function to evaluate cells | Excel Worksheet Functions | |||
Hybrid Mode
