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
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.
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.
arr = NewItem(arr, srch).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
stringToBeFound in a specific position. But I am not sure that I could understand what OP really wants accomplishing... He looks to not be moved by any (such) solution. :)
stringToBeFound As Char, will the 'string to be found' be a single character?