Why is this excel vlookup macro giving me an "object required" error?

[Nicole17]

Hello,

I'm trying to figure out how to add a vlookup to a longer macro I'm working on but I keep running into a variety of errors. I ended up creating a new Sub () to test out a very simple vlookup macro in the hopes of figuring out what's going on.

I went to this website VLOOKUP In VBA and copied exactly a sample code they provided there (only thing I changed was substituting in my own lookup value and range), but I'm still getting an "object required" error. Is there something wrong with the provided code, or do I have a setting off on my computer? I've tried to use vlookup in a macro several different ways but I keep getting assorted errors (including object required) and it's getting immensely frustrating. Any help would be greatly appreciated!

Sub Fustrated()
Dim E_name As String
E_name = "Lira"
Sal = Application.WorksheetFunction.VLookup(E_name, Admin.Range("AF3:AG12"), 2, False)
MsgBox "Number" And Sal
End Sub

 

[Jonmo1]

Is there a Sheet with the CodeName Admin, or a Worksheet Variable that has been set to a specific Sheet?

Try
Sal = Application.WorksheetFunction.VLookup(E_name, Sheets("Admin").Range("AF3:AG12"), 2, False)
Adjust Admin to reflect the actual sheet that you're trying to find E_name on.

 

[Nicole17]

That worked wonderfully, thank you so much! :D The sheet is named "Admin" and the sheet I'm trying to run the vlookup on is named "Testing".

One more question, if you have time:
Instead of bringing up a message box, I'm trying to get my macro to paste the vlookup value in another column on the Testing worksheet (ideally I'm going to get this to run a for/next loop so that the macro will automatically look up 10 values at once...). I tried adding the line of code below, but I got an invalid procedure argument. Any ideas? And thanks again for your help with the first error, I never thought I'd get that to go away!

Sub LookUpAndCopy()
Dim E_name As String
E_name = "Lira"
Sal = Application.WorksheetFunction.VLookup(E_name, Sheets("Admin").Range("AF3:AG12"), 2, False)
Sheets("Testing").Cells("K3").Value = Sal
End Sub

 

[Jonmo1]

Change Cells("K3") to Range("K3")

 

[Nicole17]

Worked perfectly, yay! Thank you! Do you know what the difference is between cells and range that caused the error?

 

[Jonmo1]

Range uses just a text string to represent column Letter and Row #
Cells uses 2 arguments, 1 for Row# and one For column Letter or Number

Cells(row#,Col letterornumber)

to get K3 with Cells, it's
Cells(3, "K")
or
Cells(3, 11)

 

[Nicole17]

Well, knowing that earlier would have saved me a LOT of trouble with the rest of this macro...

I tried to get the code to work with my for/next loop, but for some reason I'm now getting an "unable to get the vlookup property of the worksheetfunction class. Strangely enough, part of the vlookup function seems to run and paste the found data in the appropriate place! Do you have any idea why the function would suddenly stop working in a loop?!

Sub LookUpAndCopy()
Dim LastName As String
Dim b As Long
For b = 3 To 12
    LastName = Sheets("Testing").Range("B" & b)
    Sal = Application.WorksheetFunction.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
    Sheets("Testing").Range("K" & b).Value = Sal
Next b
End Sub

 

[Nicole17]

If anyone can figure out this last problem above I would be thrilled, because I've built a somewhat complicated (for me at least...) macro in parts and this vlookup is the last thing I need to tie it all together!

 

[Nicole17]

Just realized I needed to put in an error handler to deal with a situation where the looked up value does not appear in the lookup range (duh!). In my code if a value is found by vlookup the function returns a value of 15. But when I run the error handler I added instead of putting a 4 there like I intended it to, I just end up with a 15 for every value looked up, regardless of whether it's in the lookup range or not! I know I'm probably missing something really stupid here...any ideas of what?

Sub LookUpAndCopy()
On Error GoTo ErrorHandler:
Dim LastName As Range
Dim b As Long
For b = 3 To 12
    Set LastName = Sheets("Testing").Range("B" & b)
    Sal = Application.WorksheetFunction.VLookup(LastName, Sheets("Admin").Range("AF3:AG12"), 2, False)
    Sheets("Testing").Range("K" & b).Value = Sal
Next b
ErrorHandler:
    If Err.Number = 1004 Then
        Sheets("Testing").Range("K" & b).Value = "4"
        Resume Next
    End If
End Sub

 

[Jonmo1]

Try without a loop altogether..
Put the formula in the cells, and replace with the values (like copy/pastespecial/values)
The 4 is what you want returned instead of #N/A errors

With Sheets("Testing").Range("K3:K12")
    .Formula = "=IF(ISNA(VLOOKUP(B3,Admin!$AF$3:$AG$12,2,FALSE)),""4"",VLOOKUP(B3,Admin!$AF$3:$AG$12,2,FALSE))"
    .Value = .Value
End With