Alvin Poh’s Blog

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

Similar Posts:

• Understanding What Paper Quality Means
• How To Sort A Multidimensional Array By One Element In PHP
• How To Find All Unread Mail in GMail
• Keyboard Shortcut to Lock Cells in Excel (Mac Office)
• How To Edit Cell With A Keyboard Shortcut In Mac Excel