Modify line of VBA code

[Tom Jones]

Hi,

How can I change


If Cells(i, 3) = "AAA" Or Cells(i, 3) = "BBB" Or Cells(i, 3) = "CCC" Then

to take value AAA, BBB, CCC from a Range, say J2:J5

[mancubus]

do you want to check if the range J2:J5 contains any of the 3 values (AAA, BBB, CCC)?

[Tom Jones]

Thanks for replay mancubus,


do you want to check if the range J2:J5 contains any of the 3 values (AAA, BBB, CCC)?

No. In column C (in range A2:H250) are many names.
I want to copy in other sheet, only the row containing that name (AAA, BBB, CCC could be more) so need to change this line

If Cells(i, 3) = "AAA" Or Cells(i, 3) = "BBB" Or Cells(i, 3) = "CCC" Then

with something to copy that row with name from a range in sheet, say J2:Jn

[mancubus]

?


Sub vbax_61214_check_if_value_exists_in_defined_list()

Dim i As Long
Dim dList

With Worksheets("Sheet1") 'change Sheet1 to suit
dList = Application.Transpose(.Range("J2:J5").Value) 'change J2:J5 to suit
For i = 2 To 250
If UBound(Filter(dList, .Cells(i, 3).Value, , vbTextCompare)) > -1 Then
'do stuff
End If
Next
End With

End Sub


vbTextCompare: matches both AAA and aaa
vbBinaryCompare: matches AAA only

[Tom Jones]

Thank you mancubus.
It works.

[mancubus]

you are welcome
pls mark the thread as solved for future references...