Forum Discussion
Using an array to find a value in a column (VBA)
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
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
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 SubMaybe 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.
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 SubMaybe with this code. In this example the search criteria has to be changed within the code if required.
