“Unable to get the VLookup property of the WorksheetFunction Class” error

Hi all, I am trying to set the values in column B in my Workbook "Copy of Dash Board Shell" (shown in the image below) equal to the value derived from my HLOOKUP function from my data workbook shown as second image below
Copy of Dash Board Shell
Capture.PNG

Data File
capture 1.PNG

so esentially the code will find a match in the data file and copy its MIS number and paste it in the desired location on the Dash Board file, heres my code, I keep getting an error because I dont know the syntax, can someone help?

Please Login or Register  to view this content.

You don't need to select or activate ranges or sheets in your code, the only purpose those functions serve is to make your code slower.
Personally I would use Range.Find over calling Vlookup, something like.

Please Login or Register  to view this content.

I get another syntax error with that.... Capture.PNG

Originally Posted by jason.b75

You don't need to select or activate ranges or sheets in your code, the only purpose those functions serve is to make your code slower.
Personally I would use Range.Find over calling Vlookup, something like.

Please Login or Register  to view this content.

Hey Jason, Im open to anything if you think find is better I may just use that.

I got an error though, it says "Application-defined or Object-defined error" when I run the code

Attachment 423947

Do you know how to get around this?

sorry wont post .PNG anymore. What file format is best?

Jpeg is probably the safest if you do need to post an image.

I have edited my previous post to show why you had the error with xladept's suggestion.

Which line was the error caused by in my code? It was a quick untested hash so it could have a couple of typos in it.

Originally Posted by jason.b75

Jpeg is probably the safest if you do need to post an image.

I have edited my previous post to show why you had the error with xladept's suggestion.

Which line was the error caused by in my code? It was a quick untested hash so it could have a couple of typos in it.

Jason thank you so much, this worked. I know this could be asking for a lot out of you, but say for example the field for MIS number is blank, the code will give an error which is normal. How do you add an error handler within the loop that if the value is "" it keeps going to the next entry. I am going to try your Find code now and get back to you. Thanks again you saved me!

Originally Posted by jason.b75

Jpeg is probably the safest if you do need to post an image.

I have edited my previous post to show why you had the error with xladept's suggestion.

Which line was the error caused by in my code? It was a quick untested hash so it could have a couple of typos in it.

I get an error with the line
"For Each c In .Range("A3", .Cells(Rows.Count, "A").End(xlUp).Row)"

maybe is because 'c 'wasn't set equal to something yet in the code, we declared it as a range but never set the range equal to something? Thats my guess. I didnt touch your code all is the same from above

Hopefully xladept can answer that one, error handling is one of my reasons for using Find instead of Vlookup.

Using Find, this part traps any potential errors

Please Login or Register  to view this content.

Basically no match returns an empty variable (rFound Is Nothing) so we can tell the code what to do if the match is nothing or if it is something, there is no error involved here.
With Vlookup, no match automatically returns an error, so you have to trap in, either with more worksheet functions, or with vba On Error, both of which can get pretty messy.

Originally Posted by lougs7

I get an error with the line
"For Each c In .Range("A3", .Cells(Rows.Count, "A").End(xlUp).Row)"

maybe is because 'c 'wasn't set equal to something yet in the code, we declared it as a range but never set the range equal to something? Thats my guess. I didnt touch your code all is the same from above

Typo number 1 That line should be

Please Login or Register  to view this content.

c refers to each cell in the range, it doesn't need to be set, just available for the code to use.

Jason it works! Thank you kindly, you sir are awesome!