I want to find a certain string in an array and for the case that it isn't found, add it to the end of the array. Checking if the value is in the array is okay. However, I can't find a method to determine the last next free place to add the value for the case that it's missing.

Sub New_3()

Dim i As Integer
Dim stringToBeFound As Char
Dim arr(10) As Variant

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

    If arr(i) = stringToBeFound Then
    
        ' ???
    
    End If

Next i

End Sub

CodePudding user response：

Not so clear what you try doing... You also did not answer my clarifications questions.

Since As Char does not have a specific meaning in VBA, I only supposed that you need using a single character. If so, please try the next way:

Sub New_3()
 Dim mtch, stringToBeFound As String * 1
 Dim arr As Variant

 arr = Split("A,B,V,N,G,Y,U,Q,O,S,Z", ",")
 stringToBeFound = "W"
 mtch = Application.match(stringToBeFound, arr, 0)
 If Not IsNumeric(mtch) Then
    ReDim Preserve arr(UBound(arr)   1)
    arr(UBound(arr)) = stringToBeFound
 End If
 Debug.Print Join(arr, "|") 'see in Immediate Window the result
End Sub

You may use arr As Variant, but you need to load it in a different way. In order to show the result I've chosen a simpler loading way...

If you do not wand doing what the above code does, please better explain in words, with examples, what do you try accomplishing.

CodePudding user response：

Flexibilized array editing

Though you want to append non-matching search strings only to the end of a given 1-dim array, it might be fun to study a function in addition to FaneDuru's valid solution which allows to insert, too if you pass an index argument localizing where to position the new item.

This approach profits from a virtual (combo) control and its '.Add` method.

• If you only want to append a new item like in OP, it suffices to reassign arr = NewItem(arr, srch).
• If you want, however to insert an item at the third position (by using a 0-based index), pass the wanted index as further argument arr = NewItem(arr, srch, 2) or arr = NewItem(arr, srch, idx:=2).

Sub ExampleCall()
    Dim arr:  arr = Split("A,B,V,N,G,Y,U,Q,O,S,Z", ",")
    Dim srch: srch = "W"
'1) check for matching position
    Dim mtch: mtch = Application.Match(srch, arr, 0)
'2) append non-matching item
    If Not IsNumeric(mtch) Then arr = NewItem(arr, srch)
    Debug.Print "arr(" & LBound(arr) & " To " & UBound(arr) & ") = " & Join(arr, "|")
    ' ~~> arr(0 To 11) = A|B|V|N|G|Y|U|Q|O|S|Z|W
End Sub

Function NewItem()

Excpects a 1-dim array as input argument; otherwise you'd need to change sections c) and d).

Function NewItem(arr, ByVal insert As String, _
            Optional idx As Variant, Optional rebase As Boolean = True)
'Purpose: - input of 0-based idx argument: inserts item
'         - no input of idx argument:      appends item
With CreateObject("Forms.ComboBox.1")
'a) assign array to list property (no Userform ctrl needed!)
    .List = arr
'b) append or insert?
    If IsMissing(idx) Then
        .AddItem insert         ' append item
    Else
        .AddItem insert, idx    ' insert item (Index argument idx is zero - based!)
    End If
'c) change 2-dim .List values to a "flat" array
    Dim tmp: tmp = Application.Transpose(.List)
'd) return function result
    If rebase Then ReDim Preserve tmp(LBound(arr) To UBound(arr) - LBound(arr)   1)
    NewItem = tmp
End With

End Function