Is there a simple way to implement a loop until each value in a multidimensional array is not "empty" per the type - so loop until a string array is filled with values that are not empty strings, etc.
The pseudocode example of what I'm going for:
Dim MultiArray(100,100) As String
Do Until MultiArray(0,0) To MultiArray (100,100) Is Not = ""
'Operations
Loop
Is this "simple"?
For i = 0 To 100
For j = 0 To 100
'Operations
MultiArray(i, j) = 1
Next j
Next i
Function IsArrayFilled(ByRef Multi_Array() As String) As Boolean
Dim i As Long, j As Long
For i = LBound(Multi_Array, 1) To UBound(Multi_Array, 1)
For j = LBound(Multi_Array, 2) To UBound(Multi_Array, 2)
If Multi_Array(i, j) = "" Then ' If there are ANY blank strings, then terminate the function immediately and return false
IsArrayFilled = False
Exit Function
End If
Next j
Next i
' If we got through the whole array without finding any blank strings, then return true
IsArrayFilled = True
End Function
Sub Test()
Dim MultiArray(0 To 100, 0 To 100) As String
Do While Not IsArrayFilled(MultiArray)
'Operations
Loop
End Sub
Note that this is a very expensive function, as it will attempt to iterate through the entire array every time it checks, i.e. it gets progressively slower as the array is filled. I'm unsure why you need to check for empty strings and why you can't simply iterate through the array once. However, this is the only way that I know to do what you're asking.
You have a 2D array, so the only possible loop construct that will iterate all values is a nested For loop.
If I understand your problem statement correctly, you want to break out of that nested loop when you encounter an empty string value.
That's making a lot of assumptions about what's actually in that array. Let's start by assuming a row/column layout, respectively in dimension indices 1 and 2 - and because we don't know where the data came from (a worksheet range? some file? hard-coded?), let's not hard-code the lower and upper bounds of the loops:
Dim row As Long
For row = LBound(data, 1) To UBound(data, 1)
Dim break As Boolean
Dim col As Long
For col = LBound(data, 2) To UBound(data, 2)
'Operations
Dim value As Variant
value = CStr(data(row, col))
If value = vbNullString Then
break = True
Exit For
End If
Next
If break Then Exit For
Next
There's a problem though: this will break out of the loop as soon as an empty string is encountered - which may or may not be what you wanted.
If you want to stop iterating rows when you found a row where every column contains an empty string, then this isn't it. You need more precise logic. Something like this:
Dim row As Long
For row = LBound(data, 1) To UBound(data, 1)
Dim rowContents As String
Dim col As Long
For col = LBound(data, 2) To UBound(data, 2)
'Operations
rowContents = rowContents & CStr(data(row, col))
Next
If Strings.Trim(rowContents) = vbNullString Then Exit For
Next
Note that both of these loops will blow up if any "cell" contains an error value. You can guard against that using the IsError function.
But then again, this is assuming you're iterating rows. And if you're in Excel and you're iterating a 2D array where the first dimension represents rows, and you're looking to stop a loop once you've found a "row" that contains nothing but empty "cells", then the real problem you're trying to solve is "find the last used row in a range", and you're doing all this for nothing and this answer is what you really want to use:
With Sheets("Sheet1") If Application.WorksheetFunction.CountA(.Cells) <> 0 Then lastrow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Else lastrow = 1 End If End With
I could keep extrapolating about what you're really trying to achieve all day, but I'll stop here.
