COUNTIF in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
The code works fine for me if I have a defined name of MyData.
"MyData" appears as 'text' in the Excelsheet.
Click to expand...

What exactly do you mean by this statement?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
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*"")"
 
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*"")"
 
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
 
Thank you. This the one I needed. It works!

K Dales said:
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*"")"
Click to expand...
 
I mean that it appears in the formula as such, not as a range. It was not
recognized as a range by Excel.
 
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"?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to reference one cell in a range of one or more cells 4
Getting data from a closed wbook (adaptation of rondebruin's) 4
Copying one work sheet to another 3
Extract data from SQL Server table into excel using Listbox Selection as criteria 0
select range of cells in multiple colums automaticlly 1
Data type not defined 3
Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts 16
Copy Selected Text from Text Box 2

Back
Top