Find and Replace 2 cells

[NOOBY92]

Hi, I know that using find (ctrl+f) you can choose a certain value in a cell
and replace it. I was wondering if you could do this for 2 or more cells. For
example, can you tell excel to search for all instances in which a cell in
collumn B has a certain value (for example x) and the cell to the right of it
has another certain value (for example y) so that every time a cell in
collumn B has a value of x and the cell to the right of it (in collumn c) has
a value of Y, excel will replace Y with another value (for example z).

 

[Barb Reinhardt]

Without VBA, I'd probably do this with autofilter. Select the value for B
that you want and the value for C that you want and change as needed.

 

[NOOBY92]

What about with vba. Autofilter doesn't automatically change the values. If I
have over 500 values then that solution won't work.

 

[Barb Reinhardt]

Try this:

Sub Test()
Dim myRange As Range, r As Range
Dim lastRow As Long
Dim aWS As Worksheet

Set aWS = ActiveSheet

lastRow = aws.Cells(Rows.Count, 2).End(xlUp).Row
Debug.Print lastRow

Set myRange = aws.Cells(1, 2).Resize(lastRow, 1)
myRange.Select

For Each r In myRange
If LCase(r.Value) = "x" Then
If LCase(r.Offset(0, 1).Value) = "y" Then
Debug.Print r.Row, r.Column, r.Value, r.Offset(0, 1).Value
r.Offset(0, 1).Value = "z"
End If
End If
Next r

End Sub

 

[NOOBY92]

I am kind of a noob with vba so could you explain where to put in all
information (ex. name of variable, name of collumn, new variable etc)