Search and Replace via VBA
 

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

Search and Replace via VBA
 

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

"bijan" wrote:

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

Search and Replace via VBA
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bijan" wrote in message
...
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

Search and Replace via VBA
 

Thanks Mike, It works Perfect!
Regards
Bijan
"Mike" wrote:

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

"bijan" wrote:

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