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.

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.

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.

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.

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.

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.

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.

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.