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

Share on facebook
Facebook
Share on google
Google+
Share on twitter
Twitter
Share on linkedin
LinkedIn

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:

More to explorer

how i started being a minimalist

How I Started Being A Minimalist

Once, I thought that all that mattered in life were the things that you possessed. Things like your car, house, watch, clothes.

3 Responses

  1. Thanks Alvin
    You helped me too
    Your codes was exactly what I was looking for. Small and clean
    Just need to change few things like range name instead of the A:A to be faster and others lines to change columns when the first is done.
    Thanks

  2. Hi Alvin,
    Thank you for posting this code, however I am trying to get a solution for something slightly different in VBA code. I have one worksheet with column A containing names and column C containing scores and I need to display a Msgbox with all the names with the score let say equal 20. I guess I need to search in the score column and find scores that are equal to 20 and return all the matching names in a msgbox. If there are none I still need to display a Msgbox saying ” There are no names with a score equal to 20″.
    Could please help me find the correct VBA code to solve this. Thank you so much in advance.

    1. you’ll need to Google for VBA examples that show you how to ‘find all values in column’ and ‘concatenate’ all matching values into a text string that you can display in a MsgBox

Leave a Reply

Your email address will not be published. Required fields are marked *