How do you loop through an array with some empty values?

omahadivision · #1 11-25-2013, 02:44 PM

Hello,

This seems like a very simple question, but I can't figure it out. I am looping through a string array, looking for the part of the array which contains a "/" mark

Code:

i=0
Do Until i > 10
   If splitslash(i) Like "*[/]*" Then
   Range("H4").Value = splitslash(i)
       End If
    i = i + 1
    Loop
    End If

The only problem is that I don't know how many values are in the array, only that they are between 0 and 11. I always get a "subscript out of range" error message once the array reaches empty space. I've tried using null qualifiers and even filling the array with arbitrary values, but I'm totally stumped.

BobBridges · #2 11-26-2013, 05:42 PM

How long has the Like operator been around?! I never knew it existed. Oh, in SQL, of course, but not in VBA. Guess I have some more reading to do. Alas, I thought by now I knew everything.

Anyway, you could intercept the error and handle it manually, but that's a pain. What you want is the UBound function. UBound(splitslash) returns the upper bound of the first dimension of the array named 'splitslash'. An optional second argument specifies other dimensions, in case splitslash has more than one. So you'd run the loop like this:

Code:

For i = 1 to UBound(splitslash)
  If splitslash(i) Like "*[/]*" Then Range("H4").Value = splitslash(i)
  next i

If you want to put each value on a new row, try this:

Code:

r=1
For i = 1 to UBound(splitslash)
  If splitslash(i) Like "*[/]*" Then
    r = r + 1
    Cells(r, 8).Value = splitslash(i)
    End If
  next i

That puts each new value in rows 2 and up, column 8 (ie H).

macropod · #3 11-26-2013, 10:21 PM

Actually, you'd ordinarily use:

Code:

Sub Demo()
Dim i As Long
For i = 0 To UBound(splitslash)
  If splitslash(i) Like "*[/]*" Then
    Range("H4").Value = splitslash(i)
    Exit For
  End If
Next i
End Sub

or

Code:

Sub Demo()
Dim i As Long
For i = LBound(splitslash) To UBound(splitslash)
  If splitslash(i) Like "*[/]*" Then
    Range("H4").Value = splitslash(i)
    Exit For
  End If
Next i
End Sub

You'd ordinarily start at 0 because, by default, the first array element is 0. You'd only start at 1 if 'Option Base 1' has been set. The 'Exit For' is used because there's no point continuing the loop once the match has been found. The second version allows for cases where the module may or may not use 'Option Base 1'.

BobBridges · #4 11-27-2013, 10:20 AM

(Further explanation:) Omaha, Paul included an Exit statement, which is correct if you want to stop once you find "the" value with the slash in it. My version assumes there are multiple values you want to save, and keeps looking through the whole array.

As for the starting point of the array, that depends on how you defined the array. What Paul's saying is that if you define an array like this:

Code:

Dim splitslash(10)

...then VBA will define splitslash with 11 entries running from splitslash(0) to splitslash(10). I'm used to programming in other languages that assume 1 as the bottom entry, so I keep forgetting how VBA does it; to avoid the uncertainty I usually specify both limits, like this:

Code:

Dim splitslash(0 to 10)

...or "1 to 10" or "5 to 23" or whatever suits my purpose.

But no matter what you specified when you defined the array, you can always have your program use the LBound function, which mirrors UBound:

Code:

For i = LBound(splitslash) To UBound(splitslash)

macropod · #5 11-27-2013, 02:30 PM

Quote:

Originally Posted by BobBridges

What Paul's saying is that if you define an array like this:

Code:

Dim splitslash(10)

Actually, I was thinking more in terms of:

Code:

Dim splitslash
splitslash = Array("one", "two", "three")

omahadivision · #6 11-28-2013, 10:49 AM

Thanks guys! I now have plenty of methods to try

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
CORREL function with a constant array	j2b3	Excel	3	01-30-2013 01:49 AM
Convert String Array to Integer Array from a User Input?	tinfanide	Excel Programming	4	12-26-2012 08:56 PM
Apparently empty (blank) cells aren't empty	daymaker	Excel	3	03-08-2012 03:41 PM
How to reserve an array in a public function?	tinfanide	Word VBA	2	02-27-2012 06:51 AM
Complex array formula	andrei	Excel	9	02-03-2012 03:40 AM