Create loop over Excel ranges

Question

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?

Bond · Accepted Answer · 2015-08-25 12:23:59Z

2

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 ...

edited Aug 25, 2015 at 12:23

answered Aug 24, 2015 at 14:31

Bond

16.3k6 gold badges34 silver badges56 bronze badges

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

4 Comments

Peter Linden Over a year ago

thanks for your reply. Your code gives me a compile error: (wrong number of arguments or invalid property assignment. Any thoughts where this goes wrong?

Bond Over a year ago

On which line? I don't see any issues here.

Peter Linden Over a year ago

it gives an errror right at the start where I declary my funcion -> Sub cDays()

Bond Over a year ago

Are you attempting to pass a parameter to your Sub? Because you haven't defined any.

Collectives™ on Stack Overflow

Create loop over Excel ranges

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related