[RESOLVED] Excel...Returning values

[vonoventwin]

I have two sheets in Excel.
Sheet1 has Social Security Numbers in Column A
and there names in Column B. This sheet never changes.

Sheet 2 is data that changes daily, but only has SSN's and no names tied to the data. So I want this sheet to grab the cooresponding names from Sheet 1.

I want to write some kind of code that will auto-populate a new column in Sheet2. Basically, in Sheet 2 If Column C's SSN is "something", then it goes and looks at sheet 1 column A finds the SSN, grabs the name out of column B and returns it to the new column in Sheet 2.

I included a small example below... Thanks!

[Webtest]

Sorry, I can't open your zip file at this time ... here is something I threw together for you to play with. It assumes that sheet 1 is sorted on column A (SSN). It references the master Number/Name list in cells A2:B14 on Sheet 1, and on Sheet 2 it loads B2:B12 with the lookup values from A2:A12. Just a quick and dirty example of the VLOOKUP function. You will have to add the code to take care of the case where the number you are trying to look up does not exist on the source sheet.

Option Explicit
Sub Macro1()
    Dim dRange As Range  'Destination Range
    Dim sRange As Range  'Source Range
    Dim aCell As Range
    
    'Set the Range of Data on Sheet 1 (SOURCE)
    Set sRange = Sheets("Sheet1").Range("A2:B14")  '<Set this range programmatically
    'Set the Range of Data on Sheet 2 (DESTINATION)
    Set dRange = Sheets("Sheet2").Range("A2:A12")  '<Set this range programmatically
    
    'Iterate through the Destination cells loading the values looked up from the Source
    For Each aCell In dRange.Cells
        aCell.Offset(0, 1).Value = _
            Application.WorksheetFunction.VLookup(aCell.Value, sRange, 2, False)
    Next aCell
End Sub

Good Luck and Good Programming!

[vonoventwin]

Thanks but I dont think I need this. I just want to look up the SSN from Sheet 1 and return the cooresponding name. I have no clue what you posted (not to bright with Excel)

[Webtest]

Make a clean test workbook with 2 sheets. On Sheet 1 A1 put a header SSN, and in B1 put a header NAME. In rows 2 through 14 put some sample SSN/Name pairs SORTED BY SSN. On Sheet 2, in A1 put a header SSN, and in B1 put a header Name. In A2 through A12 put random SSNs from the master list.

Run the Macro and it will fill in the Names in column B on Sheet 2 from the Master list on Sheet 1. Isn't this what you wanted to do???

[vonoventwin]

hmm let me try... That's exactly what I want. Hold tight!

[Webtest]

Also ... in Excel, lookup the VLOOKUP Function in the HelpHeap.

[vonoventwin]

That works perfect, only one problem. Sometimes there are SSN in Sheet 2 that will not be in Sheet 1 (new people that I will need to add to Sheet 1). Anyway if a SSN is not found to prompt me to add that SSN with their Name to Sheet 1?? Thanks again!

[vonoventwin]

Nevermind..I will just use the Error box, then find which SSN it didn't return a name for. That will be my prompt to add that SSN to sheet 1 with their name. Thanks!

[Webtest]

I was surprised to learn that the Source array does not need to be sorted in this case! From the HelpHeap:
If range_lookup is FALSE, table_array does not need to be sorted

Also from the HelpHeap:
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

Just test your return value for #NA and code accordingly. I'm out of time here. Good Luck and Good Programming!

[Webtest]

Incidentally, the test for "#N/A" is buried way too deep in the ('2003) HelpHeap to ever find ... here it is:

If aCell.Text = "#N/A" Then
    MsgBox "No Match Was Found in the VLOOKUP Table"
Else
    MsgBox "A Match WAS found and will be processed"
End If