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]

I want you to try to really strip it down, so we can really focus on just the problem.
By that, I mean something like:
- Only have something like 10 rows of data
- Only have a few columns (obviously, the ones in question that are involved in the issue, like the formula column)
- Get rid of all VBA code and buttons that are not related to the issue
- Provide with a list the exact steps to follow to reproduce the issue

If you can do that, I will take a look and see what I can do.
If you are not able to simplify it any further, I am afraid that I probably will not be able to offer much more help.

 

[jalrs]

I want you to try to really strip it down, so we can really focus on just the problem.
By that, I mean something like:
- Only have something like 10 rows of data
- Only have a few columns (obviously, the ones in question that are involved in the issue, like the formula column)
- Get rid of all VBA code and buttons that are not related to the issue
- Provide with a list the exact steps to follow to reproduce the issue

If you can do that, I will take a look and see what I can do.
If you are not able to simplify it any further, I am afraid that I probably will not be able to offer much more help.

for sure i can, ill prepare it.
ill answer by the end of the day!

Thank you!

 

[Joe4]

for sure i can, ill prepare it.
ill answer by the end of the day!

Thank you!

Great!
The goal is to "isolate the problem" and "get rid of all the extra noise" that has nothing to do with the issue.
It is a good debugging technique, to try to eliminate everything else that has nothing to do with the issue, and focus solely on the issue.

 

[jalrs]

Great!
The goal is to "isolate the problem" and "get rid of all the extra noise" that has nothing to do with the issue.
It is a good debugging technique, to try to eliminate everything else that has nothing to do with the issue, and focus solely on the issue.

yes i understand! i'll cover all topics that you mentioned.

Thank you, honestly! Appreciate the effort Joe!

 

[jalrs]

Great!
The goal is to "isolate the problem" and "get rid of all the extra noise" that has nothing to do with the issue.
It is a good debugging technique, to try to eliminate everything else that has nothing to do with the issue, and focus solely on the issue.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Please let me know if something wasn't clear. I gave my best trying to detail everything. But sometimes we can forget something, so please address it to me so we can figure it out together.

Note that on "copy" module on both templates, you'll need to change the path of the copy.

Attached, you can find how the prototype folder should look.

I have no idea why the StransitoJoe.xlsm is that big, because i reduced it down like you asked as you will be able to see.

Thank you Joe

 

[rlv01]

A couple of comments.

1. Your file is corrupted. I got unzip errors and worksheet 'Histórico Fechados' is hidden even though the property says visible. The file is large because of Worksheet "Histórico Fechados".
26MB is unreasonably huge for a test file . You need to make your test file much much smaller. Worksheet "Histórico Fechados" has ~8 million cells of data. You must reduce this by 99.99% down to
the bare minimum needed to demontrate your problem.

 

[jalrs]

A couple of comments.

1. Your file is corrupted. I got unzip errors and worksheet 'Histórico Fechados' is hidden even though the property says visible. The file is large because of Worksheet "Histórico Fechados".
26MB is unreasonably huge for a test file . You need to make your test file much much smaller. Worksheet "Histórico Fechados" has ~8 million cells of data. You must reduce this by 99.99% down to
the bare minimum needed to demontrate your problem.

i will have a look, thanks for commenting

 

[jalrs]

A couple of comments.

1. Your file is corrupted. I got unzip errors and worksheet 'Histórico Fechados' is hidden even though the property says visible. The file is large because of Worksheet "Histórico Fechados".
26MB is unreasonably huge for a test file . You need to make your test file much much smaller. Worksheet "Histórico Fechados" has ~8 million cells of data. You must reduce this by 99.99% down to
the bare minimum needed to demontrate your problem.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thanks, think now it's good. Have a look please.

@Joe4 updated version since rlv01 pointed out the source of the file being too big

 

[Joe4]

The issue is with this line here:

    ws2.UsedRange.Offset(1).ClearContents

It is clearing the entire range of your destination sheet, including the formulas you have in column F!
That is why they are disappearing.

You can use VBA to add those formula back in at the end of your code, like this:

Sub filterApoioSPEXCEL()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("TemplateExcelVlookUpFormula.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("A2:D" & lr1)
    
        .AutoFilter 2, "Apoio SP"
        .AutoFilter 3, "Em tratamento"
        .Offset(1).Copy ws2.Cells(lr2, 1)
        
        .AutoFilter
        
    End With
    
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A10").EntireRow.Delete
    
'   Add formulas to column F
    Range("F2:F" & lr2 - 1).FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-5]:C[-4],2,0))"
    
End Sub

 

[jalrs]

The issue is with this line here:

    ws2.UsedRange.Offset(1).ClearContents

It is clearing the entire range of your destination sheet, including the formulas you have in column F!
That is why they are disappearing.

You can use VBA to add those formula back in at the end of your code, like this:

Sub filterApoioSPEXCEL()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
  
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("TemplateExcelVlookUpFormula.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("A2:D" & lr1)
  
        .AutoFilter 2, "Apoio SP"
        .AutoFilter 3, "Em tratamento"
        .Offset(1).Copy ws2.Cells(lr2, 1)
      
        .AutoFilter
      
    End With
  
    lr2 = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    ws2.Range("A" & lr2 & ":A10").EntireRow.Delete
  
'   Add formulas to column F
    Range("F2:F" & lr2 - 1).FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],TAB_FDB!C[-5]:C[-4],2,0))"
  
End Sub

Hey Joe,

Thanks for assisting me! Since today is national holiday, i will comeback to you tomorrow when i get to the office and give feedback about it.

Thanks!