Macro to find and replace

[Thembela01]

Hi Gurus,

Im looking for a macro to and find replace whats in Column A with Column D. For instance It takes A2 and go to Column D to find a match, if it finds a match in Column D , it does nothing. If it doesnt find a match it replaces with the corresponding Payno on B2. If no match for UserID then replace with with Payno.

UserId	Payno	Role	Validated List
10038217	10038217		ANDRIES
ABELM	1001721		ABELM
ADOLPHINAS	1002416		10038217
ADRIG	00161725		BEN

 

[Kaper]

Try:

Sub IfNotFoundReplaceWithB()
Dim columnsAB As Variant, columnD As Variant, MatchResult As Variant, i As Long
With ActiveSheet ' you may want to use it on inactive one
  columnsAB = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 2).Value
  columnD = .Cells(2, 4).Resize(.Cells(.Rows.Count, 4).End(xlUp).Row - 1, 1).Value
  For i = 2 To UBound(columnsAB)
    MatchResult = Application.Match(columnsAB(i, 1), columnD, 0)
    If IsError(MatchResult) Then columnsAB(i, 1) = columnsAB(i, 2)
  Next i
  .Cells(1, 1).Resize(UBound(columnsAB), 2) = columnsAB
End With
End Sub

The result shall be:

UserId

10038217​

ABELM

1002416​

161725​

 

[DanteAmor]

Try this:

Sub find_and_replace_v1()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF(COUNTIF(D:D," & .Address & ")," & .Address & "," & .Offset(, 1).Address & ")")
  End With
End Sub

In some versions of excel an IF is required at the beginning of the function evaluate, then try:

Sub find_and_replace_v2()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},IF(COUNTIF(D:D," & .Address & ")," & .Address & "," & .Offset(, 1).Address & "))")
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Thembela01]

Thanks it worked