Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Error 1004 with lookup.

0

Been trying to get vlookup to work in VBA, I can't and don't know why. I put the exact vlookup in a cell as a test and it works. I have tried activating and selecting the specific worksheet to no avail. here's the code:

Code_Goes_Here

Sub find_ingredients_in_list()

Range("j2").ClearContents

Range("j2") = "testing"

Sheets("input").Activate

food = WorksheetFunction.vlookup("olive", Range("j4:,k8"), 2, False)

Cells(24, 2) = food

End Sub

Code_Goes_Here

Having fought with this I think "match" might work better for my purpose but I would like to know why my vlookup isn't working. Thanks

DOH!!

excelvlookup
MS Office Version: Office 365
posted by: baddog1016 (rep: 16) on Fri Jan 16, 2026 at 10:53 am
Answer
Discuss

Discussion

Glad I could help.
Thanks for selecting my answer.

Cheers   :-)
WillieD24 (rep: 723) Jan 17, '26 at 1:06 am
Add to Discussion

Answers

0
Selected Answer

Hi baddog1016,

Just taking a quick look at the code you have posted, and assuming it is accurate, the problem lies in your range reference: Range("j4:,k8")

As posted you have a comma ( , ) after the colon ( : ). Remove the comma and it should work.

Also in your line: Cells(24,2) = food I recommend adding " .Value " after " Cells "

Cheers   :-)

posted by: WillieD24 (rep: 723) on Fri Jan 16, 2026 at 3:06 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login