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

 

[jalrs]

We can do our own calculation of what row we need to copy down to if you can answer the following:
What column of data will always be populated with data (for any row that has data)?

If i do understand your question, since i am filtering data according to the responsible department, column AT will always have data

Thanks

 

[Joe4]

OK, try replacing the section we had at the end of the code to populate these formulas with this:

'   Add formulas to column AY
    Dim lr3 As Long
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    If lr3 > 1 Then
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
    End If

 

[jalrs]

OK, try replacing the section we had at the end of the code to populate these formulas with this:

'   Add formulas to column AY
    Dim lr3 As Long
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    If lr3 > 1 Then
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
    End If

Well i runned and it didnt work, i runned again it didnt work, i runned for a third time and it did work, i assigned the macro to a button, stopped working.

what is going on?

i might just drop full vba code here:

Option Explicit
Sub filtroApoioSP()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("T_ApoioSP.xlsm")
    
    Set ws1 = wb1.Worksheets("Stock Trânsito")
    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, "Apoio SP"
        .AutoFilter 47, "Em tratamento"
        .Offset(1).Copy ws2.Cells(lr2, 1)
        
        With ws1.Range("BH6:BH" & lr1)
        
           .Copy ws2.Cells(2, 49)
           
        End With
        
        .AutoFilter
        
    End With
    
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A1001").EntireRow.Delete
    
    Dim lr3 As Long
    
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    
    If lr3 > 1 Then
    
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
            
    End If
        
End Sub

 

[Joe4]

Add a message box after the lr3 calcualtion to see what it is returning, and see/confirm if that is indeed the last row with data, i.e.

    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    MsgBox lr3

(I hope you are picking up on some of these VBA debugging techniques I am showing you, like using MsgBox to check the values of variables at key points).

 

[jalrs]

Add a message box after the lr3 calcualtion to see what it is returning, and see/confirm if that is indeed the last row with data, i.e.

    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    MsgBox lr3

(I hope you are picking up on some of these VBA debugging techniques I am showing you, like using MsgBox to check the values of variables at key points).

1860

It is indeed the last row with data from the database workbook

 

[Joe4]

1860

It is indeed the last row with data from the database workbook

So it should be populating AY2:AY1860 with the vlookup formula.
Is it not doing that?

One other thought. Since you are dealing with multiple workbooks and worksheets here, you may need to include these references in this section to make sure we are doing the calculations in the proper place and placing the formula on the proper sheet.

 

[jalrs]

So it should be populating AY2:AY1860 with the vlookup formula.
Is it not doing that?

One other thought. Since you are dealing with multiple workbooks and worksheets here, you may need to include these references in this section to make sure we are doing the calculations in the proper place and placing the formula on the proper sheet.

Ok. Lets get back to the same page, i think i got lost after that msgbox lr3, even tho i get the technique.

1. The code i pasted is on Workbook 1.
2. Row 1860 is the last row on Workbook 1 with data.
3. The code i pasted filters the information to Workbook 2, according to department
4. On Workbook 2, in this department, i have 27 records found from Workbook 1. Therefore last populated row is 28 because row 1 contains headers.
5. So it should populate AY2:AY28

Am i applying your lines on wrong workbook, i'm assuming?

This code here is on a module on the 2nd workbook, that copies the template after getting the filtered data to a destination path

Sub primeiroTrimestre()

Dim path As String
Dim filename As String

path = "C:\Users\joafrodrigue\Desktop\prototipo\Difusao\"
filename = "ST_até31032022_Apoio SP"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True

End Sub

Should i add the lines you said to this sub instead?

Thanks

 

[jalrs]

I tried the option above and it didnt work too

 

[Joe4]

OK, I think we are on the wrong workbook when trying to run this code.
Try this:

'   Add formulas to column AY
    wb2.Activate
    ws2.Activate
    Dim lr3 As Long
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    If lr3 > 1 Then
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
    End If

 

[jalrs]

OK, I think we are on the wrong workbook when trying to run this code.
Try this:

'   Add formulas to column AY
    wb2.Activate
    ws2.Activate
    Dim lr3 As Long
    lr3 = Cells(Rows.Count, "AT").End(xlUp).Row
    If lr3 > 1 Then
        Range("AY2:AY" & lr3).FormulaR1C1 = _
            "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-50]:C[-49],2,0))"
    End If

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