Forum Discussion
Using an array to find a value in a column (VBA)
Thanks for your reply. The solution works for what it is intended however, I will be using the part of the code that I'm requesting on a bigger project and the structure that is requested has to use an array and find values based on that array.
At the very least I know I have to do the following:
Dim FilterCriteria as variable
FitlterCriteria = Array("119885", "185430", "170479")
Dim c as range
For each c in range("A:A")
"Find if value matches array FilterCriteria and add an X to the end of it"
The text in the " " is the thext that im struggling to write the code for.
Sub FindBob()
Dim i As Integer
Dim arrNumbers As Variant
Dim arrtext() As Variant
Range("I:I").Clear
arrtext() = Array("100000", "200000", "300000")
For i = 1 To 15
arrNumbers = Filter(arrtext, Cells(i, 1))
Cells(i, 9).Value = arrNumbers
If Cells(i, 9).Value <> "" Then
Cells(i, 9).Value = Cells(i, 9).Value & "X"
Else
Cells(i, 9).Value = Cells(i, 1).Value
End If
Next i
End Sub
Maybe with this code. In this example the search criteria has to be changed within the code if required.
Thanks for your reply, the file that I'm working with has a variable amount of rows, in the code that you're providing, I see that you're using a 1 TO 15 being that theres only 15 rows, what could I change in order to adapt to an X number of rows every time i run the code? (actual 3 columns of an example of a file im going to work with attached)
Lets say that the array for the file i attached is
Array( "105701", "106177", "106182", "106583", "106709", "106722", "106909", "106991", "107025", "107111", "107222", "107500", "107777", "108888", "10677", "106777")"
and the results for the cells with "X" added need to be on the same column. So if the value on C1 was 107025, after the macro it would be 107025X.
File attached.