Using an array to find a value in a column (VBA)

Copper Contributor

Hello, I need find a value in Column A using an array of values, if the value is found, i need to simply replace it with the same value + "X" using VBA 

craygoza92_0-1647610900852.png

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.

Sample file attached

 

Thanks.

4 Replies

@craygoza92 

Sub x()

Dim i As Integer
Dim j As Integer

For i = 2 To 11
For j = 3 To 5
If Cells(i, 1).Value = Cells(1, j).Value Then
Cells(i, 10).Value = Cells(i, 1).Value & "X"
Exit For
Else
Cells(i, 10).Value = Cells(i, 1).Value
End If

Next j
Next i


End Sub

 

Maybe with these lines of code. Enter the search value in range C1:E1 and click the button in cell D9 to start the macro.

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.

@craygoza92 

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.

@OliverScheurich 

 

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.