Process (Code debug) VBA lookup function

[Aliq2014]

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.
</SPAN>
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>
3. Dim x, lRow As Long</SPAN>
6. lRow = Cells(Rows.Count, 1).End(xlUp).Row 'to find the last row</SPAN>
9. On Error Resume Next</SPAN>
12. For x = 1 To lRow</SPAN>
13. Cells(x, 2) = Application.WorksheetFunction.VLOOKUP(Cells(x, 1), Workbooks("EFMA_template.xlsm").Worksheets("Sheet1").Range("B:H"), 6, False)</SPAN>
14. Next x</SPAN>
17. End Sub</SPAN>

</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?

Thanks..

 

[WarPigl3t]

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?

 

[Aliq2014]

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.

 

[WarPigl3t]

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)

 

[WarPigl3t]

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)

 

[Aliq2014]

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

 

[WarPigl3t]

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

 

[Aliq2014]

'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.

 

[WarPigl3t]

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

 

[Aliq2014]

Yes I noticed that after I stepped into the code. I really appreciate your help thanks!