Error 2042 problem with vlookup

[dfeteau]

I'm having a problem trying to use a simple vlookup request and match it to
another variable.

Two tables on two sheets. The problem I'm having is that when the vlookup
request to the array comes back as an error when it shouldn't. I've looked
at this code 100 times and I can't find an error. Any help??

Sheet1
500 Description A
501 Description B
502 Description C
503 Description D

Sheet2
500 5/16/2011 TEST0
500 5/16/2011 TEST1
500 5/16/2011 TEST2
501 5/16/2011 TEST3
503 5/16/2011 TEST4
503 5/16/2011 TEST5
504 5/16/2011 TEST6

Sub KeepTrying()

Sheets("Sheet2").Activate
'Get  Count
Range("A1").Select
    i = 0
    Do Until ActiveCell.Value =  ""
        ActiveCell.Offset(1, 0).Select
        i = i + 1
     Loop

'Create Array using count
Dim tableA() As Variant
ReDim  tableA(1 To i, 1 To 3)
Range("A1").Select
x = 1
Do Until x = i +  1
    tableA(x, 1) = ActiveCell.Offset(0, 0).Value
    tableA(x, 2) = ActiveCell.Offset(0, 1).Value
    tableA(x, 2) = ActiveCell.Offset(0,  2).Value
    ActiveCell.Offset(1, 0).Select
    x = x +  1
Loop

'Check
Sheets("Sheet1").Activate
Range("A1").Select
     Do Until ActiveCell.Value = ""
        For d = 1 To i
        code =  ActiveCell.Value
        codeCheck = Application.VLookup(d, tableA, 1, False)  - PROBLEM!!!
        If code = codeCheck Then
             ActiveCell.Offset(0, 3).FormulaR1C1 = "Y"
            ActiveCell.Offset(1,  0).Select
        End If
        Next
    Loop

End Sub

 

[RoryA]

I would suspect that one set of numbers is actually stored as text but you are looking up an actual number. In the lookup table, are the numeric codes left or right aligned by default? (ie without any manual alignment applied)

 

[AlphaFrog]

Try something like this...

Sub KeepTrying()

    Dim cell As Range

    ' Check
    With Sheets("Sheet1")
        For Each cell In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            If Not Sheets("Sheet2").Range("A:A").Find( _
                                     What:=cell.Value, _
                                     LookIn:=xlValues, _
                                     Lookat:=xlWhole, _
                                     MatchCase:=False) Is Nothing Then
                cell.Offset(0, 3).Value = "Y"
            End If
        Next cell
    End With

End Sub

 

[AlphaFrog]

In your original code, I suspect that VLookup is expecting a cell range where you have tableA and not an array variable.

 

[dfeteau]

I would suspect that one set of numbers is actually stored as text but you are looking up an actual number. In the lookup table, are the numeric codes left or right aligned by default? (ie without any manual alignment applied)

I thought so too but I checked the Locals window and it shows the first dataArrray variable as a variant/double. The same with the variable i'm searching with.

 

[dfeteau]

Try something like this...

Sub KeepTrying()

    Dim cell As Range

    ' Check
    With Sheets("Sheet1")
        For Each cell In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            If Not Sheets("Sheet2").Range("A:A").Find( _
                                     What:=cell.Value, _
                                     LookIn:=xlValues, _
                                     Lookat:=xlWhole, _
                                     MatchCase:=False) Is Nothing Then
                cell.Offset(0, 3).Value = "Y"
            End If
        Next cell
    End With

End Sub

This isn't bad but what I ultimately want to do is to add that variable to another array. Will doing it this way allow me to do that?

 

[dfeteau]

Try something like this...

Sub KeepTrying()

    Dim cell As Range

    ' Check
    With Sheets("Sheet1")
        For Each cell In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
            If Not Sheets("Sheet2").Range("A:A").Find( _
                                     What:=cell.Value, _
                                     LookIn:=xlValues, _
                                     Lookat:=xlWhole, _
                                     MatchCase:=False) Is Nothing Then
                cell.Offset(0, 3).Value = "Y"
            End If
        Next cell
    End With

End Sub

This worked fine. Thanks a lot!!

 

[AlphaFrog]

Thank you for the feedback. I'm glad it works for you.