This Excel VBA function allows you to take a column of values (e.g. ID numbers) in one worksheet, and use that to get corresponding values that are in a second worksheet. If you understand SQL, it’s something like:
SELECT b.country FROM idtable as a, countrytable as b WHERE a.id=b.id
I’ll explain the scenario further with screenshots, but basically we have one worksheet having client IDs, and another worksheet having client IDs with their corresponding countries.
The first worksheet looks like this. Notice the country column is column A and we’re looking to fill that up. Our IDs are all in column C:
And this is how the second worksheet looks like. Notice the headers are removed for convenience’s sake. We’re going to search column A using the IDs in worksheet 1, then grab every row’s country value.
Here’s the VBA code. Just stick it into your VBA editor, and run it to see what it does. A few things to note:
- Find this line For Each Cell In Range(“C2:C8”), and change C2:C8 to the range of cells that contain the IDs that you want to search for.
- Find this line With Sheets(“Sheet2”).Range(“A:A”), and change A:A to whichever column that you’re trying to search in.
Sub findCountry()
Dim FindString As String
Dim Rng As Range
For Each Cell In Range("C2:C8")
FindString = Cell.Value
If Trim(FindString) <> "" Then
'The 2nd worksheet is assumed to be Sheet2. Change this if it is not the case
With Sheets("Sheet2").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
'In Sheet 2: This line shifts 5 cells to the right and gets the country value
TheCountry = ActiveCell.Offset(0, 5).Value
'In Sheet 1: The country value is pasted into the cell 2 cells to the left of the cell containing the ID
Cell.Offset(0, -2).Value = TheCountry
Else
MsgBox "Nothing found"
End If
End With
End If
Next
End Sub