How to do a vlookup in VBA?

**kjy1989** · 04-08-2013, 03:48 AM

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:


Option Explicit

Sub testing()
Dim Description As String
Dim Answer As String


Description = ActiveCell.Offset(0, 3)
' remember the activecell is in the voltage column, hence the description column is at activecell.offset(0,3)

If ActiveCell.Offset(0, 0) = "220" Then
Answer = VLOOKUP(activecell.offset(0,-1), [xyz.xlsm]PRICELIST!$1:$1048576,9,FALSE)

If ActiveCell.Offset(0, 0) = "110" Then
Answer = VLOOKUP(activecell.offset(0,-1), [xyz.xlsm]PRICELIST!$1:$1048576,10,FALSE)

If ActiveCell.Offset(0, 0) = "380" Then
Answer = VLOOKUP(activecell.offset(0,-1), [xyz.xlsm]PRICELIST!$1:$1048576,11,FALSE)

End If

ActiveCell.Offset(0, 3) = Description & Answer

end sub

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** · 04-08-2013, 03:54 AM

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** · 04-08-2013, 04:28 AM

I reckon

Sub testing()
   Dim Description            As String
   Dim Answer                 As String
   Dim lCol                   As Long

   Description = ActiveCell.Offset(0, 3).Value
   ' remember the activecell is in the voltage column, hence the description column is at activecell.offset(0,3)

   Select Case ActiveCell.Value
      Case 220
         lCol = 9
      Case 110
         lCol = 10
      Case 380
         lCol = 11
   End Select
   Answer = Application.VLookup(ActiveCell.Offset(0, -1).Value, Workbooks("xyz.xlsm").Sheets("PRICELIST").Range("A:K"), lCol, False)

   ActiveCell.Offset(0, 3).Value = Description & Answer

End Sub

**kjy1989** · 04-09-2013, 10:53 PM

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?