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

 

[rlv01]

Try using Application.Vlookup instead of Application.WorksheetFunction.Vlookup

Sub myvlookup()
    
    Dim pWS As Worksheet, tWS As Worksheet
    Dim pLR As Long, tLR As Long, x As Long
    Dim datarng As Range
    Dim VLResult As Variant
    
    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
        
      VLResult = Application.VLookup(pWS.Range("AX" & x).Value, datarng, 2, 0)
      pWS.Range("AY" & x).Value = VLResult

    Next x
    
End Sub

 

[jalrs]

Try using Application.Vlookup instead of Application.WorksheetFunction.Vlookup

Sub myvlookup()
   
    Dim pWS As Worksheet, tWS As Worksheet
    Dim pLR As Long, tLR As Long, x As Long
    Dim datarng As Range
    Dim VLResult As Variant
   
    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
       
      VLResult = Application.VLookup(pWS.Range("AX" & x).Value, datarng, 2, 0)
      pWS.Range("AY" & x).Value = VLResult

    Next x
   
End Sub

Hello @rlv01, firstly i'd like to thank you for your time.

Secondly, the code works, i could work around that even tho it is not the expected final output, my first code might have been a little bit misleading.
My thing is, vlookup macro is supposed to run when a value is picked on AX from the dropdown list, and it autofills AY column. What is happening at the moment is that i need to fill all of AX column, and then run the macro to fill AY column. I don't want this extra click if possible, is it?

I could tell is related with the for, just don't know how to set it up.
I wait for your reply

Thank you again!

 

[Joe4]

OK, this code NEEDS to be placed in the proper sheet module.
In order to ensure that, go to the "Pendentes" worksheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the following code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pWS As Worksheet, tWS As Worksheet
    Dim pLR As Long, tLR As Long, x As Long
    Dim datarng As Range

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Exit if update is not in column AX under row 1
    If (Target.Row = 1) Or (Target.Column <> 50) Then Exit Sub
   
    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)

    On Error Resume Next
    Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(Target.Value, datarng, 2, 0)
    On Error GoTo 0

End Sub

As you manually updated values in column AX on your Pendentes sheet, the code will automatically populate column AY.

 

[jalrs]

OK, this code NEEDS to be placed in the proper sheet module.
In order to ensure that, go to the "Pendentes" worksheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the following code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim pWS As Worksheet, tWS As Worksheet
    Dim pLR As Long, tLR As Long, x As Long
    Dim datarng As Range

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
 
'   Exit if update is not in column AX under row 1
    If (Target.Row = 1) Or (Target.Column <> 50) Then Exit Sub
 
    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)

    On Error Resume Next
    Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(Target.Value, datarng, 2, 0)
    On Error GoTo 0

End Sub

As you manually updated values in column AX on your Pendentes sheet, the code will automatically populate column AY.

ok this works, but now following this code i have a related issue:

i have a macro where it hides sheets, makes a copy of current workbook under different name, and closes the workbook.
when i open it, the code doesnt follow the copy. is it because it has a different name? or is it because the "TAB_FDB" sheet is hidden on the copy?

the need of the copy, is because the current one works as a template, and the copy is meant to be shared to end users so they can populate column AX and get the returned AY value as your code already does

thanks for your assist!

 

[Joe4]

i have a macro where it hides sheets, makes a copy of current workbook under different name, and closes the workbook.

What format is it saving it in, and what is the exact file name it is saving it too (please be sure to include the file extension).

when i open it, the code doesnt follow the copy. is it because it has a different name? or is it because the "TAB_FDB" sheet is hidden on the copy?

It should not have an issue doing a VLOOKUP into a hidden sheet.
Are you sure that VBA is enabled when you open this copy?

Where exactly are you opening the file (on the web, in SharePoint, or on your PC)?
VBA does not work in all these places.

 

[jalrs]

What format is it saving it in, and what is the exact file name it is saving it too (please be sure to include the file extension).


It should not have an issue doing a VLOOKUP into a hidden sheet.
Are you sure that VBA is enabled when you open this copy?

Where exactly are you opening the file (on the web, in SharePoint, or on your PC)?
VBA does not work in all these places.

names vary according to quarters, i have 4 buttons for each quarter, because i couldn't find any assistance regarding this matter, so i did the "easy" way as you can see:

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

Sub segundoTrimestre()

Dim path As String
Dim filename As String

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

End Sub

Sub terceiroTrimestre()

Dim path As String
Dim filename As String

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

End Sub

Sub quartoTrimestre()

Dim path As String
Dim filename As String

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

End Sub

end users are company employees, they will open the file on their computers after i send them the emails with the generated copy with correct name according to each quarter. the templates are called T+DepartmentName, in this specific case, TApoio SP.xlsm

hope i answered your questions, i wait your reply

thanks!

 

[Joe4]

You cannot save the files as "xlsx" files. Those do not support VBA.
If you want to maintain the VBA code in the copies, you need to save to one of the VBA supported Excel formats, like "xlsm" or "xlsb".

See here: File formats that are supported in Excel

 

[jalrs]

You cannot save the files as "xlsx" files. Those do not support VBA.
If you want to maintain the VBA code in the copies, you need to save to one of the VBA supported Excel formats, like "xlsm" or "xlsb".

See here: File formats that are supported in Excel

Thanks for the suggestion Joe4!

Additionally, is there anything that i can do in order to carry the formula through when converting my template (xlsm) to my copy (xlsx)? When i tried to apply the excel formula to the column, it didn't follow up with the copy.

I wait for your reply

Thanks again!

 

[Joe4]

Additionally, is there anything that i can do in order to carry the formula through when converting my template (xlsm) to my copy (xlsx)? When i tried to apply the excel formula to the column, it didn't follow up with the copy.

I am not sure what you mean by this. Can you explain in more detail?
You are not putting any formula in the sheet with your VBA, you are pasting a value.