CODEWITHSUNDEEP
vbaexcel
Peter Linden
Peter Linden

Reputation: 21

Create loop over Excel ranges

I would like to create a function which loops over certain cell values. If the value contains an "X", the count should increase by 1.

The syntax should be something like this:

 Sub CountVars()

 Dim range As Variant
 Dim ranges As Variant

 Dim count as Integer

 'set array with ranges
 range = Array("c3", "f3", h3)

 For Each range In ranges

   count = 0
   'if range = "X" then
   count = count + 1
   End If    
 Next

But I cant get this working. Any thoughts on how I can achieve my goal?

Upvotes: 1

Views: 45

Answers (1)

Bond
Bond

Reputation: 16311

You can define your ranges within the Range() function directly. No need for a string array. For example:

Dim r As Range, count As Long

For Each r In Range("C3,F3,H3")
    If r.Value = "X" Then count = count + 1
Next

Note that string comparison done this way is case-sensitive. If you need to check for x or X, you can convert to the same case or use a function like StrComp() with the vbTextCompare parameter.

If UCase$(r.Value) = "X" Then ...
' or
If StrComp(r.Value, "X", vbTextCompare) = 0 Then ...

Upvotes: 2

Related Questions