Solved: Replace function

[justdriving]

Hi,

Col B has Emp Name and Col C has Emp ID.

---Col B ---------- Col C ------
---AAAA---------1111111---------
---BBBB----------2222222---------

In Col G, I have only Emp ID. Then, how can I replace every Emp ID with Emp Name in same column G using Information from Col B without an error. If I don't find an employee name there in Col B for Emp ID in Col G, then I must ignore that cell and move to next cell in that Col G.

[mikerickson]

=INDEX(B:B, MATCH(G1,C:C,0),1) will return the name that matches the number in G1.
Drag down for the rest of the column.

[justdriving]

Hi, it seems there was some problem. May be I have not done it correctly in attachment. Please advice how can I view result in Col H using VBA?.

[Bob Phillips]

It looks to me you have done it perfectly and it works fine.

[justdriving]

Hi,

Col B has Emp Name and Col C has Emp ID.

---Col B ---------- Col C ------
---AAAA---------1111111---------
---BBBB----------2222222---------

In Col G, I have only Emp ID. Then, how can I replace every Emp ID with Emp Name in same column G using Information from Col B without an error. If I don't find an employee name there in Col B for Emp ID in Col G, then I must ignore that cell and move to next cell in that Col G.


Hi Bob,

I wanted to replace values, instead of writing values in adjacent column. Requesting for help in VBA.

[justdriving]

Hi, I am requesting response if I have done anything wrong due to which my Post # 3 was edited by Aussiebear.

[shrivallabha]

Probably this code:
Public Sub ReplaceColG()
Dim lLR As Long
Dim r As Range
lLR = Range("G" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
If Range("G" & i).Value <> "" Then
Set r = Columns("C:C").Find(What:=Range("G" & i).Value, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then
Range("G" & i).Value = r.Offset(, -1).Value
End If
End If
Next i
End Sub