Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Why does this (part of a) macro not work? Who can help me to find a solution?
Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
The code works fine for me if I have a defined name of MyData.
"MyData" appears as 'text' in the Excelsheet. What exactly do you mean by this statement? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Wim VL" <Wim wrote in message ... Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Dim MyData As Range
Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(" _ MyData.Address & ",""*less*"")" -- Regards, Tom Ogilvy "Wim VL" <Wim wrote in message ... Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Yes, you diagnosed the problem correctly: the variable MyData is part of your
VBA code and Excel won't recognize it typed into a formula. What you want, really, is for the formula to contain a reference in standard Excel notation e.g. A1:B2. You can get this from MyData.Address; so try this: Selection.Formula = "=COUNTIF(" & MyData.Address & ",""*less*"")" -- - K Dales "Wim VL" wrote: Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Since every thing is hard-coded anyway, you could simplify that to just
Range("D16").Formula = "=COUNTIF(D4:D14,""*less*"")" HTH, Bernie MS Excel MVP "Wim VL" <Wim wrote in message ... Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Thank you. This the one I needed. It works!
"K Dales" wrote: Yes, you diagnosed the problem correctly: the variable MyData is part of your VBA code and Excel won't recognize it typed into a formula. What you want, really, is for the formula to contain a reference in standard Excel notation e.g. A1:B2. You can get this from MyData.Address; so try this: Selection.Formula = "=COUNTIF(" & MyData.Address & ",""*less*"")" -- - K Dales "Wim VL" wrote: Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
I mean that it appears in the formula as such, not as a range. It was not
recognized as a range by Excel. "Chip Pearson" wrote: The code works fine for me if I have a defined name of MyData. "MyData" appears as 'text' in the Excelsheet. What exactly do you mean by this statement? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Wim VL" <Wim wrote in message ... Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF in VBA
Thank you, Tom. But this one didn't work. The one of K Dales did. Could the
reason be the absence of the "&" before "MyData"? "Tom Ogilvy" wrote: Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(" _ MyData.Address & ",""*less*"")" -- Regards, Tom Ogilvy "Wim VL" <Wim wrote in message ... Why does this (part of a) macro not work? Who can help me to find a solution? Dim MyData As Range Dim MyResult As Range Set MyData = Range("D4:D14") Set MyResult = Range("D16") MyResult.Select Selection.Formula = "=COUNTIF(MyData,""*less*"")" 'This one does not work.It doesn't recognize "MyData" as a range. "MyData" appears as 'text' in the Excelsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif or Something Else? | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |