Search and Replace via VBA

[bijan]

Hi Experts,
Several days ago,I asked a question about find & replace with conditions and
got good answer about Findlast record.after I tried that code I found out it
is useful for unique record not my data,
Now I ask that question again with this sample data:
___A___B___C___
1 Q1 0 SCR
2 Q2 0 NOT
3 Q2 1 NOT
4 Q3 0 RNG
5 Q4 0 TOP
6 Q4 2 FOR
7 Q4 2 FOR
8 Q5 0 TOP
I would like to change value of "FOR" (In Rows 6,7) to "OK", How can I find
Q4s (in Column A with value 2 in column B) and change value of them in
column C.
Thanks in advance
Bijan

 

[Mike]

try this
Sub ok()
Dim c As Range
Dim rng As Range

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each c In rng
If c.Value = "Q4" And c.Offset(0, 1).Value = 2 Then
c.Offset(0, 2).Value = "OK"
End If
Next
End Sub

 

[Don Guillett]

Have you looked in the vba help index for FIND or FINDNEXT.
Or, you can use a for each loop

for each c in range("a1:a8")
if c="Q4" and c.offset(,1)=2 then c.offset(,2)="OK"
next c

 

[bijan]

Thanks Mike, It works Perfect!
Regards
Bijan

try this
Sub ok()
Dim c As Range
Dim rng As Range

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each c In rng
If c.Value = "Q4" And c.Offset(0, 1).Value = 2 Then
c.Offset(0, 2).Value = "OK"
End If
Next
End Sub