How to loop to find and replace with the specific cells

[Linda Lo]

Maybe someone could help me, Have some code and find only the first Cells(185,23) has work ,the Cells(186,23) (187.23) can't work....

Sheets("PAU REJECT").Select

   For Each vDEC In Range("W185:W220")

If vDEC <> "" Then

    AA = Range("W65536").End(xlUp).Row

    For Ad = 185 To AA 

    vDEC = Cells(Ad, 23)

    vDECamt = Cells(Ad, 24)

    EXEADM = Cells(Ad, 26)

    EXEADMname = Cells(Ad, 27)

    EXEADMNo = Cells(Ad, 28)

    EXEADMType = Cells(Ad, 29)

    If vDEC <> "" Then

     Sheets("PAU Maintenance Record").Select

     Columns(4).Select

            Selection.Find(What:=vDEC, After:=ActiveCell, LookIn:=xlFormulas, _

            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

            MatchCase:=False, SearchFormat:=False).Activate

    DecAC = Cells(ActiveCell.Row, 4)

    DecACamt = Cells(ActiveCell.Row, 18)

    DecFollowUp = Cells(ActiveCell.Row, 23)

  If DecFollowUp = "" And DecAC = vDEC And DecACamt = vDECamt Then

    Cells(ActiveCell.Row, 23).Select

    Selection.Replace What:="", Replacement:="Advice", LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

    Cells(ActiveCell.Row, 6).Select

    Selection.Replace What:="*", Replacement:=EXEADMname, LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

    Cells(ActiveCell.Row, 7).Select

    Selection.Replace What:="*", Replacement:=EXEADMNo, LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

   Cells(ActiveCell.Row, 8).Select

    Selection.Replace What:="*", Replacement:=EXEADMType, LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

 

    Cells(ActiveCell.Row, 4).Select

    Selection.Replace What:=vDEC, Replacement:=EXEADM, LookAt:=xlPart, _

    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

    ReplaceFormat:=False

 End If

End If

Next Ad

  

  End If

 

Next vDEC

 

[plettieri]

Hello Lina La
Just a few comments and possible things to look at to help correct your code as presented.

Your code seems to have a nested loop --- For Each loop that passes through the range W185:W220, but within this loop, you're also using another For loop that starts from row 185 and goes up to the end--- last row. This might be causing some conflict.

and

Inside the inner loop, you're overwriting the variable vDEC . It could be that you're losing the original value from the outer loop.

Hope this helps
plettieri

 

[Linda Lo]

Thanks, I think need time to rewrite again in order to stop conflict.