How to do a vlookup in VBA?

[kjy1989]

Usually, i would use a hotdog analogy when asking questions here so people could understand my problem more, but in this case it seems i have no choice but to ask my actual question.

So the content of cell A1 is "SEZC100F3020"
the content of cell B1 is "220"
the content cell c1 and d1 are irrelevant, i just put it here so the activecell.offset numbers later would make sense.
cell E1 is the description column.

My plan would be is the user will put the active cell in B1, which contains the 220. Just for FYI, this is the voltage column, and the code "SEZC100F3020" is a model of a circuit breaker that has many different voltages (220, 110, and 380). So if the user puts the active cell in the voltage column, he then runs a macro that will do a vlookup on the code SEZC100F3020, the table array of the lookup would be another excel file (pricelist file) that will contain the necessary data of the voltage value in the activecell. The value of the vlookup will be stored using variable "Answer" and it will be added to the description column. so here is my code:

Please Login or Register  to view this content.

so what i want is that the answer will be included in the description. I know the vlookups are incorrect, but i wrote that to have an idea of what i want to call up.

[AndyLitch]

Hi
Would it be possible for you to upload a copy of the file so we can get a clearer understanding?.. It will speed up the process and is more likely to achieve the best possible solution.
Just be mindful of the fact that you're not supposed to upload sensitive or secret information

1. Select Edit Message or Reply
2. Select "Go Advanced" at the bottom right of the message area
3. Select the paper clip on the menu bar
4. Follow the wizard instructions for uploading a file.

I may have a nice surprise for ya

[JosephP]

I reckon

Please Login or Register  to view this content.

[kjy1989]

JosephP, I am amazed at how simple you wrote the code because i imagined it to be very complex in my head, what's even more amazing is that it's working haha.

One problem though is that this code will work if the workbook xyz.xlsm is open and i wanted to know what is the prefix code to write in the code Workboox("xyz.xlsm").Sheets("PRICELIST").Range etc... for it to work even if it is not open? Pardon me for my amateurish question. In my example the xyz file would be located in:

C:\Users\Kevin\Google Drive\Pricelist\xyz.xlsm

how do i put that in vba format?

[JosephP]

you would have to actually put a formula into the target cell-you can convert it into a value afterwards