0

so I have a spreadsheet and I want to count how many instances of certain words there are in a range of cells. Usually with Excel formulae I can just do:

Countif(Range("D3:D20"), "*bob*")

And this will return the number of cells that contain "bob"

I was hoping to use this within VBA, and do it for all the words stored in an array. I tried:

x = Application.WorksheetFunction.Countif(Range("D3:D20"), "*array(i)*")

within a loop but it doesn't find any instances.

For completion, 

x = Application.WorksheetFunction.Countif(Range("D3:D20"), "*bob*")

works as expected.

How can I pass the array elements as a string recognisable by the excel function? Thanks in advance

Improve this question
4
  • 1
    String your search parameter together with & like so: x = Application.WorksheetFunction.Countif(Range("D3:D20"), "*" & array(i) & "*") Commented Oct 22, 2019 at 9:46
  • Perfect, thank you that worked. I'm still learning VBA. Commented Oct 22, 2019 at 9:48
  • Do you want to add this as an answer so I can close the question? Commented Oct 22, 2019 at 9:49
  • 1
    No worries, happy to help. I'll type up an answer with an explanation as well if you give me a moment. Commented Oct 22, 2019 at 9:51

1 Answer 1

Reset to default
2

The reason 

x = Application.WorksheetFunction.Countif(Range("D3:D20"), "*array(i)*")

won't work is because excel sees everything inside of the quotes as part of the string. So it will literally search for *array(i)*. This allows us to search for things like brackets ( in our sheet without throwing errors that the brackets are part of an incomplete statement.

To omit this you pass the evaluation of the array outside brackets, which means it is evaluated and passes its solution. Then you concatenate the wildcards you need on both sides using & like so:

x = Application.WorksheetFunction.Countif(Range("D3:D20"), "*" & array(i) & "*")

Note that there are brackets around the * but not the array statement. Say array(i) is "BOB" this will now search for "*" & BOB & "*", concatenated by &: *BOB*.

Please note this method works for text strings, but is unreliable for numeric values.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.