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]

Joe,

Words can't describe how grateful I am, it is indeed working as intended.

Thank you so much for this.

As a last request, not really a request, but i just dont want to break this.

if i add under your line:

dim ws3 as worksheet
set ws3=wb2.worksheets("Readme")
ws3.activate

will it auto return to ws3 without compromising the rest of the code? it is just to save one click, if you see this breaking the code just tell me and i won't perform the action

Thank you very much again

I don't see a problem with doing that.
Just note that you probably need to be on wb2 before activating ws3.
So if you aren't already in wb2, you would need:

wb2.Activate
ws3.Activate

 

[jalrs]

I don't see a problem with doing that.
Just note that you probably need to be on wb2 before activating ws3.
So if you aren't already in wb2, you would need:

wb2.Activate
ws3.Activate

after running the code you provided i am on workbook 2 since its the one we activate before adding the vlookup r1c1 formula.

so yes, makes sense to me that i only need to declare ws3 and activate it therefore.

Thanks for assisting me once again Joe! All the best to you!

 

[Joe4]

You are welcome.

 

[jalrs]

You are welcome.

Joe I tried to adapt the code to another sheet where i have some less columns, but the formula R1C1 seems to be wrong here, could you provide some help?

Sub filtroLF()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("ST_TEMPLATE_LF")

    Set ws1 = wb1.Worksheets("Stock")
    Set ws2 = wb2.Worksheets("Pendentes")
    
    ws2.UsedRange.Offset(1).ClearContents
    
    Dim lr1 As Long, lr2 As Long
    
    lr1 = ws1.Cells(Rows.Count, 1).End(3).Row
    lr2 = ws2.Cells(Rows.Count, 1).End(3).Row + 1
    
    With ws1.Range("A5:AV" & lr1)
    
        .AutoFilter 46, "LF"
        .AutoFilter 47, "Em tratamento"
        
        With ws1
        
            .Range("A6:T" & lr1).Offset(1).Copy ws2.Cells(2, 1)
            .Range("W6:AC" & lr1).Offset(1).Copy ws2.Cells(2, 21)
            .Range("AF6:AV" & lr1).Offset(1).Copy ws2.Cells(2, 28)
            .Range("BH6:BH" & lr1).Offset(1).Copy ws2.Cells(2, 45)
            
            
        End With
        
        .AutoFilter
        
    End With
    
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A1001").EntireRow.Delete
    
    wb2.Activate
    
    ws2.Activate
    
    Dim lr3 As Long
    
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    
    If lr3 > 1 Then
    
        Range("AU2:AU" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-46]:C[-45],2,0))"
            
    End If
    
End Sub

tell me if you need to see the sheet, i will send through dropbox.

Thank you!

 

[Joe4]

Joe I tried to adapt the code to another sheet where i have some less columns, but the formula R1C1 seems to be wrong here, could you provide some help?

Did you turn on your Macro Recorder and try manually entering the VLOOKUP formula, as it needs to appear?
If you do that, Excel will generate the EXACT VBA code for the formula that you need, and you can just copy and paste that part of the VBA code to your main procedure.

 

[jalrs]

Did you turn on your Macro Recorder and try manually entering the VLOOKUP formula, as it needs to appear?
If you do that, Excel will generate the EXACT VBA code for the formula that you need, and you can just copy and paste that part of the VBA code to your main procedure.

Hi Joe,

I went to: options - formulas - and thicked the r1c1 box, and then looked at formula and adapted it, so from [-50] and [-49] i got [-46] and [-45] on the c1 part, and i also adapted the range to my new range, since im working with less columns. I dont know what is it, maybe you could have a look later if i provide the file?

Thanks

 

[Joe4]

Hi Joe,

I went to: options - formulas - and thicked the r1c1 box, and then looked at formula and adapted it, so from [-50] and [-49] i got [-46] and [-45] on the c1 part, and i also adapted the range to my new range, since im working with less columns. I dont know what is it, maybe you could have a look later if i provide the file?

Thanks

No, that is not what I am saying. You actually do not need anyone's help to figure out what your VLOOKUP formula should be in this case - you can let Excel figure it out for you, using the method I described here in post 34: VBA VlookUp not working

If you turn on the Macro Recorder, and manually enter an example of your formula, exactly as you need it to appear on your sheet, Excel will give you the exact VBA code you need!
Please try it!

 

[jalrs]

No, that is not what I am saying. You actually do not need anyone's help to figure out what your VLOOKUP formula should be in this case - you can let Excel figure it out for you, using the method I described here in post 34: VBA VlookUp not working

If you turn on the Macro Recorder, and manually enter an example of your formula, exactly as you need it to appear on your sheet, Excel will give you the exact VBA code you need!
Please try it!

Ok i will try it that way. Since on my post 35 i addressed the same way of finding the r1c1 and you didnt say anything regarding it, i thought it was also an option.
I will try and see how it goes.

Thanks

 

[Joe4]

Ok i will try it that way. Since on my post 35 i addressed the same way of finding the r1c1 and you didnt say anything regarding it, i thought it was also an option.
I will try and see how it goes.

Thanks

The method I describe in post 34 is a REALLY important trick you will want to learn, so you can learn how to do these things yourself, instead of asking the same type of questions over-and-over.
My goal is not to spoon-feed you the answers all the time, but rather teach you the techniques you can use to solve it on your own.

 

[jalrs]

The method I describe in post 34 is a REALLY important trick you will want to learn, so you can learn how to do these things yourself, instead of asking the same type of questions over-and-over.
My goal is not to spoon-feed you the answers all the time, but rather teach you the techniques you can use to solve it on your own.

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!