Excel Lookup Problem!!!!

[Salvatore]

I am trusting on the best of minds in VBA to assist on this problem!!!


OK! I have account numbers in column "A" that I need to compare with similar account numbers in column "B".

Once I locate a match I need to program the spreadsheet to grab the ID number in column "C" that are associated with the account numbers in col. "B"!

HAVE I CONFUSED YOU YET!!

Now I attempted, with no success, to loop through the range of cells in col. "A", and look for a match in col. "B". If rngACell.value = rngBCell.value then perform a VLookUp for the ID # in col. "C".

I am confident that someone has come across this problem before, or it may not even be a problem....I must be getting close to the solution...

Thank you in advance..

[Salvatore]

This is the code I have so far:

Dim shtSurvey As Worksheet, rngCell As Range, rngCurrent As Range
Dim rngID As Range, rngNew As Range
Set shtSurvey = Application.Workbooks("srvy1.xls").Worksheets("srvy1")

Set rngCurrent = shtSurvey.Range("n2:n32")
Set rngID = shtSurvey.Range("r2:r50")
Set rngNew = shtSurvey.Range("p2:p32") ' p2 - p32

For Each rngCell In rngCurrent
    rngNew.Value = Application.WorksheetFunction.VLookup(rngCell, Range("ID"), 2, False)
    
    
Next rngCell

What is happening, is that it is filling the Range("p232")' p2 - p32 with just one matching account number....it looks as if it is running through all the matchs on the VLookUP function, but it is not filling the individual cells on the range...?????



Any suggestions?

[alex_read]

NOW I'm a little confused !!!?!?!?
Okay to make this simpler, we'll work off this example, I've created a spreadsheet with the following:

1	6	a
2	2	b
3	7	c
4	4	d
5	8	e

I'm taking it that the you want to see if each cell in the first range is within the second? i.e. So you'll check whether the value for cell A1 is within range B1 to B5?

Or are we on about adjacent cells here, i.e. rather than the whole range of column B, are you just evaluating the value of cell A1 against B1, the value of cell A2 against B2 etc?

[Salvatore]

You got it, Alex.....sorry about confusing you...

Your example will work...I need to start at the first cell in the first column and check for a match within the cells of B1 to B5( from your example).

Then if there is a match found like A1 & B3, then take the ID number "c" (from your example) and place it in a cell next to "A1"...

What do you think...crazy hugh!

Thanx

[alex_read]

Okay then, create a new spreadsheet & paste into the cells the 3 columns I've put above, then place a commandbutton on the sheet and add this code to it...

Private Sub CommandButton1_Click()
    Dim intCellCounter As Integer
    
    For intCellCounter = 1 To 5
        Range("D" & intCellCounter).FormulaR1C1 = "=LOOKUP(RC[-3],RC[-2]:R[4]C[-2])"
        If IsNumeric(Range("D" & intCellCounter).Value) Then
            MsgBox "The value of cell A" & intCellCounter & " was found between " & _
            "the range B1:B5, in row number " & intCellCounter & "." & vbCrLf & _
            "The associated value within the 3rd column is " & Range("C" & intCellCounter)
        End If
    Next intCellCounter
End Sub

Let me know if this is what you're after!

[alex_read]

Just saw the second part to your post there, this will place only the matched/correct values into column D, and won't show you any messageboxes...

Private Sub CommandButton1_Click()
    Dim intCellCounter As Integer
    
    For intCellCounter = 1 To 5
        Range("D" & intCellCounter).FormulaR1C1 = "=LOOKUP(RC[-3],RC[-2]:R[4]C[-2])"
        If Not IsNumeric(Range("D" & intCellCounter).Value) Then
            Range("D" & intCellCounter).Value = ""
        End If
    Next intCellCounter
End Sub

[Salvatore]

OK Alex I took your suggestion and placed the table starting at cell "D1" and entered the code you created into a Command Button...

The result is the column "D" went blank! I wonder if has to do with the FormulaR1C1????

[alex_read]

Ah sorry, should've put that better okay, place the values above into the cell starting at cell A1 - the returned values get created in column D, whereas the data evaluated is held in the first 3 columns.

Please see the attached file & this should make a bit more sense!