Vlookup Macro Help Please!

[matt9man]

I am newbie at vba and I am trying to get my vlookup using a macro to only return the NA# with the lookup value it used in another sheet. My goal then is too loop it so can do that through 1000's of rows. I first tried a "find" but my lookupvalues have //.. so "find" does not work but here is the vlookup macro I have:

Sub Macro2()
Dim Res As Variant

lookupval = Worksheets("Sheet1").Range("A1").Value
lookuprng = Worksheets("Sheet2").Range("A:B").Value

Res = Application.VLookup(lookupval, lookuprng, 1, False)
If IsError(Res) = False Then
Else

lookupval.Copy

Sheets("Compare Result").Range("A1").Select
ActiveSheet.Paste

End If

End Sub

 

[Kaps_mr2]

what happens if you use vlookup with the same arguments but in Excel rather than VBA ? thanks

Kaps

 

[Jonmo1]

Welcome to the board...

Vlookup is overkill when the goal is just to verify the value exists in another column.
Try using MATCH for that purpose...

Also, the lookuprng needs to be a Range object, not a string or a value..

Try this

Sub Macro2()
Dim Res As Variant, lookupval As Variant
Dim lookuprng As Range

lookupval = Worksheets("Sheet1").Range("A1").Value
Set lookuprng = Worksheets("Sheet2").Range("A:A")

Res = Application.Match(lookupval, lookuprng, 0)
If IsError(Res) Then
    Sheets("Compare Result").Cells(Rows.Count, "A").End(xlup).Offset(1).Value = lookupval
End If
End Sub

Hope that helps.<!-- / message -->

 

[matt9man]

Hey thanks for the help... its now returning a subscript error on line

Sub Macro3()
Dim Res As Variant, lookupval As Variant
Dim lookuprng As Range
lookupval = Worksheets("Sheet1").Range("A1").Value
Set lookuprng = Worksheets("Sheet2").Range("A:A")
Res = Application.Match(lookupval, lookuprng, 0)
If IsError(Res) Then
Sheets("Compare Result").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = lookupval
End If
End Sub

Any ideas and also another thought is how to make it loop?

Thanks again

 

[Jonmo1]

do you get "Subscript Out Of Range" ?

That means the sheet in question was not found.
Check spelling and for extra spaces

"Hello" Vs " Hello" Vs "Hello "

 

[matt9man]

I am dumb I figured out my error wrong sheet name.

 

[Jonmo1]

Try this to make it loop

Sub Macro2()
Dim Res As Variant, lookupval As Variant
Dim lookuprng As Range, c As Range
Dim LR As Long
LR = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set lookuprng = Worksheets("Sheet2").Range("A:A")
For Each c In Sheets("Sheet1").Range("A1:A" & LR)
    lookupval = c.Value
    Res = Application.Match(lookupval, lookuprng, 0)
    If IsError(Res) Then
        Sheets("Compare Result").Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = c.Value
    End If
Next c
End Sub

 

[matt9man]

Its working thanks a lot Jonmo!! I spent 9 hours on this yesterday next time I'll just ask for help. ...Thanks again!

 

[matt9man]

Two questions I first want to be able to copy that entire row that lookup value is associated in and copy where I currently have it going below. Second how do I input a cell 3 (col A) to the left of the copied cells which are in col D. The text that I want to be inputed is"ExtraActuals." Thanks

Sub ExtraActuals()

Dim Res As Variant, lookupval As Variant
Dim lookuprng As Range, c As Range
Dim LR As Long
LR = Sheets("Actual").Cells(Rows.Count, "A").End(xlUp).Row
Set lookuprng = Worksheets("Expected").Range("A3:A1500")
For Each c In Sheets("Actual").Range("A3:A" & LR)
lookupval = c.Value
Res = Application.Match(lookupval, lookuprng, 0)
If IsError(Res) Then
Sheets("Compare Result").Cells(Rows.Count, "D").End(xlUp).Offset(5).Value = c.Value
End If
Next c
End Sub

Thanks