HWZ Forums

Login Register FAQ Mark Forums Read

Need help in macro to find and replace if conditions are met

Reply
 
LinkBack Thread Tools
Old 17-04-2019, 07:12 PM   #1
Member
 
Join Date: Jan 2001
Posts: 348
Need help in macro to find and replace if conditions are met

Hello Gurus,

Appreciate your expertise in create macro to find and replace if certain conditions are met.

Col_A        Col_B        Col_C
aa            Depa        Depb
bb            Depa        Depc
bb            Depb        Depi
cc            Depc        Depj
cc            Depd        Depk
aa            Depc
aa            Deph
aa            Depj 

Conditions:

1. If any text in column b match any of the list in column C
2. and column a is "aa",
3. then replace "aa" with "yy"

Thanks very much for the help.

Last edited by chankh70; 17-04-2019 at 10:38 PM.. Reason: Add table
chankh70 is offline   Reply With Quote
Old 18-04-2019, 03:54 PM   #2
Master Member
 
Join Date: Apr 2003
Posts: 4,524
Here is the excel macro code.
'All data in Sheet1 'Data start from row 1 Sub Match() Dim i As Integer, j As Integer For i = 1 To ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row For j = 1 To ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row If ThisWorkbook.Sheets("Sheet1").Cells(i, 2) = ThisWorkbook.Sheets("Sheet1").Cells(j, 3) Then If ThisWorkbook.Sheets("Sheet1").Cells(i, 1) = "aa" Then ThisWorkbook.Sheets("Sheet1").Cells(i, 1) = "yy" End If Exit For End If Next j Next i End Sub
Results after running the macro.
aa Depa Depb
bb Depa Depc
bb Depb Depi
cc Depc Depj
cc Depd Depk
yy Depc
aa Deph
yy Depj

Last edited by peterchan75; 18-04-2019 at 03:58 PM..
peterchan75 is offline   Reply With Quote
Old 18-04-2019, 05:45 PM   #3
Member
 
Join Date: Jan 2001
Posts: 348
Someone have provided me with alternate code similar to yours.

Sub replAA() Dim rw As Long For rw = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Not IsError(Application.Match(Cells(rw, "B"), Range("C:C"), 0)) And Cells(rw, "A") = "aa" Then Cells(rw, "A") = "yy" Next rw End Sub
Thank-You and Appreciation.
chankh70 is offline   Reply With Quote
Reply
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ. Forum members and moderators are responsible for their own posts.

Please refer to our Terms of Service for more information.


Thread Tools

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are On