How To Search For ID Values In A Different Worksheet With Excel VBA

by Alvin Poh | Tue, Oct 4, 2011

Programming & Code


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:

excel vba search for value

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.

excel vba search for 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:

Tags: , , , , , , , , , , , , , , , ,

This post was written by:

- Alvin Poh lives in Singapore, and is interested in marketing, techy stuff, and likes to just figure out how the two can work with each other. He can also be found on Google+.

Get Alvin's Report On How To Blog Successfully - Free!

Leave a Reply

*