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