VBA VlookUp not working

[jalrs]

Hello all!

My problem concerns VBA Vlookup. Code simply doesn't work.
For context: I have two worksheets. One called "Pendentes", the other is called "TAB_FDB". I need to assign a vlookup, so column AY from worksheet "Pendentes" gets autofilled according to column AX picked value. Column AX has a dropdown list where we can pick one value and then we get a value returned on AY(automatically). The matching pair AX->AY, is on the "TAB_FDB" worksheet table. This table is on columns A:B, where A1 and B1 are headers of the table.

My code:

sub myvlookup ()

dim pWS as worksheet, tWS as worksheet
dim pLR as long, tLR as long, x as long
dim datarng as range

set pWS = Thisworkbook.Worksheets("Pendentes")
set tWS = Thisworkbook.Worksheets("TAB_FDB")

pLR = pWS.Range("A" & rows.count).end(xlup).row
tLR = tWS.Range("A" & rows.count).end(xlup).row

set datarng = tWS.Range("A2:B" & tLR)

for x = 2 to pLR

on error resume next
pWS.Range("AY" & x).Value = Application.WorksheetFunction.Vlookup(pWS.Range("AX" & x).Value, datarng, 2, 0)

next x

end sub

any help is greatly appreciated

thanks,
Afonso

 

[Joe4]

Oh, actually it was my bad, I misunderstood a question regarding the column that would be always populated no matter what, issue solved.
To conclude this here, is there any really difference between your way, turning on macro recorder etc, and going to file,options,formulas and enabling the r1c1 box?

Thanks!

Yes, there can be a big difference, depending on the formula!

That just shows you what the formula looks like on the worksheet in R1C1 format, but does not show you how it needs to look in VBA to apply it to your sheet!

For example, let's say we have the following formula in cell G2:

=IF(F2="","",D2+E2)

If we use your option, it just shows us this in the formula box:

=IF(RC[-1]="","",RC[-3]+RC[-2])

That just shows us what the formula looks like on the sheet in R1C1 format, but does NOT show us how we need to enter the formula in VBA code.

If you use my option, you will see that it shows you something like this:

    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-3]+RC[-2])"

Notice the difference in the formulas! The formula portion in red is what you would actually need to put in your VBA code, not the formula garnered by your method.

Since double-quotes are also used as text qualifiers in VBA code, they need to be differentiated from literal double-quotes in your formula.
VBA does this by doubling them up. To me, I can find this confusing at times. But the good news is that you do NOT have to figure it out yourself!
If you use the method, I gave you, you can make Excel do all the hard work for you, and all you need to do is copy the formula you recorded.

Also note that if you already have the formula entered in somewhere on your sheet, you do not need to re-type the whole thing.
Simply turn on your Macro Recorder, go to the cell with the formula, press F2 to put it in edit mode, and then click enter.
That will "re-enter" the formula and capture it in VBA code.

 

[jalrs]

Yes, there can be a big difference, depending on the formula!

That just shows you what the formula looks like on the worksheet in R1C1 format, but does not show you how it needs to look in VBA to apply it to your sheet!

For example, let's say we have the following formula in cell G2:

=IF(F2="","",D2+E2)

If we use your option, it just shows us this in the formula box:

=IF(RC[-1]="","",RC[-3]+RC[-2])

That just shows us what the formula looks like on the sheet in R1C1 format, but does NOT show us how we need to enter the formula in VBA code.

If you use my option, you will see that it shows you something like this:

    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-3]+RC[-2])"

Notice the difference in the formulas! The formula portion in red is what you would actually need to put in your VBA code, not the formula garnered by your method.

Since double-quotes are also used as text qualifiers in VBA code, they need to be differentiated from literal double-quotes in your formula.
VBA does this by doubling them up. To me, I can find this confusing at times. But the good news is that you do NOT have to figure it out yourself!
If you use the method, I gave you, you can make Excel do all the hard work for you, and all you need to do is copy the formula you recorded.

Also note that if you already have the formula entered in somewhere on your sheet, you do not need to re-type the whole thing.
Simply turn on your Macro Recorder, go to the cell with the formula, press F2 to put it in edit mode, and then click enter.
That will "re-enter" the formula and capture it in VBA code.

Alright Joe, thanks for a nice detailed explanation!

The last paragraph was a crucial one, I, indeed, had removed the formula I had there, started macro, wrote it again, stopped macro!

Thanks!

 

[Joe4]

You are welcome.