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

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

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