Mar 18 2022 06:41 AM - edited Mar 18 2022 07:55 AM
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.
Mar 18 2022 07:00 AM
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.
Mar 18 2022 07:54 AM - edited Mar 18 2022 07:55 AM
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.
Mar 18 2022 08:42 AM
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.
Mar 18 2022 09:07 AM
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.