Process (Code debug) VBA lookup function


New Member
Sep 10, 2014
Hi, I’m currently working on a macro using the vlookup function.</SPAN>

Vlookup formlula:</SPAN>
Workbooks: A)Parking_Template.xlsm – contains 55 columns (A,B….AJ……BC)</SPAN></SPAN>
B) EFMA_Template.xlsm- contains 8 columns (A,B</SPAN>,C,D,E,F,G</SPAN>,H</SPAN>)</SPAN>
Formula: </SPAN>"=VLOOKUP(AJ:AJ,'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)".</SPAN>

The formula looks at column “AJ” which is the lookup value, table array EFMS _Template.xlsm]Sheet1'!$B:$H, index to return 6, exact match.
I would like to automate the process by coding a macro that uses the vlookup function.

Someone already assisted me a code snippet and I modified:</SPAN>

  1. Sub FindVal()</SPAN>
  2. Dim x, lRow As Long</SPAN>
  3. lRow = Cells(Rows.Count, 1).End(xlUp).Row 'to find the last row</SPAN>
  4. On Error Resume Next</SPAN>
  5. For x = 1 To lRow</SPAN>
  6. Cells(x, 2) = Application.WorksheetFunction.VLOOKUP(Cells(x, 1), Workbooks("EFMA_template.xlsm").Worksheets("Sheet1").Range("B:H"), 6, False)</SPAN>
  7. Next x</SPAN>
  8. End Sub</SPAN>

I'm having an issue understanding the loop. The above code is not working correctly. Im new to programming and vba. Would someone please help me troubleshoot this issue?


Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I'm not understanding your VLookUp command. Help me understand.
"=VLOOKUP(AJ:AJ,'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)"
So AJ:AJ is the lookup value. Why is it AJ:AJ and not a specific cell like AJ2? I've never seen a lookup like you made it. I see your lookup range is $B$H. The output is 6 or in other words it is column G. And you are looking for an exact match. Explain AJ:AJ please.

Also, Cells(x, 2). Does this mean that your lookup formula is in the B column?
Last edited:
Upvote 0
Im looking at the entire column "AJ:AJ" rowns one to many. Both worksheets are dynamic. My lookup formula should be in the BC column. Sorry forgot to mentioned.
Upvote 0
Also, you are trying to use worksheet.function commands. I don't like to use those with vlookup because there is a high probability that your code will encounter an error. I've tried many times to figure out a way to override the errors, but was unsuccessful. I instead come up with a workaround. I insert the vlookup formula into the cell, then i take the value and insert it into the cell. Like this

Cells(x, 2) = "=VLOOKUP(AJ" & x & ",'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)"
Cells(x, 2) = Cells(x, 2)
Upvote 0
If the formula I wrote about in the last message works when you insert it into your desired cell, then it will work when you insert the formula into the cell using VBA. Then when you do Cells(x, 2) = Cells(x, 2) it will remove the formula and just paste the lookup value. Use your original formula though, not the one I modified.

Cells(x, 2) = "=VLOOKUP(AJ:AJ,'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)"
Cells(x, 2) = Cells(x, 2)
Upvote 0
It worked, the only issue is that insrted into column B and i would like to inret the value in column BC, do zi do this; Cell(x,55) 55 refers to column BC
Upvote 0
Yes you do. But I don't like using Cells(). It's confusing. Insead you should consider this...
Range("BC" & x).value = "=VLOOKUP(AJ:AJ,'[EFMS _Template.xlsm]Sheet1'!$B:$H,6,FALSE)"
Range("BC" & x).value = Range("BC" & x).value
Upvote 0
'one last question the vlookup works perfect now. The only concern is that the header is being replace with "N/A I would like to keep the header intact.
Upvote 0
The header is being used in your Vlookup because you told your For loop to begin on row 1. Change For x =1 to For x = 2
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back