CODEWITHSUNDEEP
arraysvbaloopstype-mismatch
M. Stag
M. Stag

Reputation: 79

VBA Looping through a dynamically generated string array (type mismatch)

I am using this function to generate a string array and it is working correctly to output a string array:

Dim tmp As String
Dim arr() As String

If Not Selection Is Nothing Then
   For Each cell In Selection
      If (cell <> "") And (InStr(tmp, cell) = 0) Then
        tmp = tmp & cell & "|"
      End If
   Next cell
End If

If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)

Debug.Print tmp
arr = Split(tmp, "|")

The problem I am having is I am then trying to use the generated array to loop through the set of strings I am searching for in a spreadsheet. I need to dynamically generate this array. The only way I know how to loop through the array is using a variant and have written the code like this:

'Create IRAF array
Dim test_col As Range
Set test_col = Range(CStr(testSearch.Offset(1, 0).Address), Range(CStr(testSearch.Address)).End(xlDown))
test_col.Select
test_arr = create_array(test_col)
Debug.Print test_arr

'Loop for the Test Cycles
Dim iGen As Variant
Dim iCurrent As Variant
Dim i As Integer
iGen = test_arr
For Each iCurrent In iGen
    Set Search = Cells.Find(What:=iCurrent, _
        After:=Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
    If Not Search Is Nothing Then...

I realize the issue I am having is that the array is being returned from the function as an array of strings and then I am trying to set a Variant type equal to the string array. Is there some way to convert the string array to a variant or otherwise create a loop for the dynamically generated string array? The problem I am trying to work around is that I need to dynamically generate the array of strings since the string search criteria could change on a file by file basis.

Upvotes: 0

Views: 755

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

It sounds like your main challenge is how to loop through your string array. The following code shows how to do this:

   For i = LBound(test_arr) To UBound(test_arr)
      'your logic here
      'you would refer to an element like this test_arr(i)
   Next

Upvotes: 1

Related Questions