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]

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.

if there is an alt way to achieve this, without VBA, since my copy is required to be xlsx.

like i tried applying excel vlookup formula to column AY, but when i generated the copy after filtering the data, the formula didn't follow the copy, i mean this formula: =IF(AX2="";"";VLOOKUP(AX2;TAB_FDB!A:B;2;0))

hope i was clear enough, let me know

thanks!

 

[Joe4]

like i tried applying excel vlookup formula to column AY, but when i generated the copy after filtering the data, the formula didn't follow the copy, i mean this formula: =IF(AX2="";"";VLOOKUP(AX2;TAB_FDB!A:B;2;0))

Are you still running the VBA code, and that is converting your formula to a hard-coded value?
If so, try that formula and do NOT run any VBA code.

If that does not work, please show us exactly what you are doing, i.e. walk us through an example, showing us some sample data and the filtering you are doing.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jalrs]

Are you still running the VBA code, and that is converting your formula to a hard-coded value?
If so, try that formula and do NOT run any VBA code.

If that does not work, please show us exactly what you are doing, i.e. walk us through an example, showing us some sample data and the filtering you are doing.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Working on a sample for you. Can't really use xl2bb since data is sensitive. Give me a few minutes and ill come back to you with a dropbox

thanks joe!

 

[Joe4]

Can't really use xl2bb since data is sensitive

Please use this tool, if at all possible! It makes it much easier for us to help you, as we can then copy the data structure, exactly as you have it, to our side.
Simply replace any sensitive data with "dummy data".

 

[jalrs]

Please use this tool, if at all possible! It makes it much easier for us to help you, as we can then copy the data structure, exactly as you have it, to our side.
Simply replace any sensitive data with "dummy data".

i would need to ask permission to use xl2bb tool since i'm working on the company laptop, that's why i choose this way, i'm sorry.

regarding the data structure, don't think you'll have any problems regarding that. i gave my best doing this prototype (prototipo2)

for context:

main folder: prototipo2 ; 2 sub folders inside main folder(Templates + Difusao2) + STransitoCopy.xlsm
- sub folder Templates: TApoio SP2.xlsm + TApoio SP3.xlsm
-sub folder Difusao2: empty

TApoio SP2.xlsm is the file with vlookup EXCEL formula =IF(AX2="";"";VLOOKUP(AX2;TAB_FDB!A:B;2;0)) on column AY
TApoio SP3.xlsm is the file with vlookup VBA on sheet "Pendentes" -> your code

Readme Worksheets: First "row" Button: Updates the filtered data from STransitoCopy.xlsm
Second "row" of buttons: Makes a copy of the template as a .xlsx, and renames it according to each quarter of the year 1oTrimestre = 1st Quarter and so on (copied to sub folder Difusao2)
Third "row" button: hides the sheets we don't want endusers to see, saves the file, and closes the file (sub folder Difusao2)

So, when doing this step by step, i want to send the the files from Difusao2 to the ApoioSP department so end users can populate column AX and get returned a value on AY.

After generating the copies and saving the files by both Vlookup methods (Excel and VBA) i noticed the formulas don't follow the copies.
Regarding VBA method, you already said that i needed to save the copy file as .xlsm or .xlb

Additionally i asked if there was anything i could do, like an altway, regarding that excel method for example, so that the formula would follow with the copy of .xlsx file.

Hope i was clear enough, and sorry again for not using xl2bb tool, even tho i think my dropbox prototype will do exactly the same.

Thanks again, let me know if something

 

[Joe4]

I will have to look at it later when I have access to my home computer.
My workplace security does not allow us to download any files from the internet, so I cannot download your file from my current location.

 

[jalrs]

I will have to look at it later when I have access to my home computer.
My workplace security does not allow us to download any files from the internet, so I cannot download your file from my current location.

all good joe! Thanks for your assistance and sorry for not using xl2bb tool.

Waiting to hear back from you!

Best regards,
Afonso

 

[jalrs]

I will have to look at it later when I have access to my home computer.
My workplace security does not allow us to download any files from the internet, so I cannot download your file from my current location.

Do you have any news?

Thanks and good night!

 

[Joe4]

Do you have any news?

Thanks and good night!

No, not really. It might be partially because it is late at night here, but I am utterly confused by your three files.
None of them seem to "Worksheet_Change" I created for you, and the buttons and worksheets are in a different language.
So I am having a very difficult time following along here.
I think we are talking back and forth, but not really communicating well.

Note that there is a Questions in Other Languages forum here, where you can post questions in your native language, if that might help.
Someone who speaks and understand your language may be able to understand the workbook better.

If you want to pursue this further in this thread, then I have this proposition for you.
Strip your problem done to the "bare bones". What I mean is try to make a real simple example, with minimal data and minimal code, that shows the problem you are having.
Let's try getting rid of all extra stuff and focus just on this issue.
Can you create a super simple example that shows your problem?

 

[jalrs]

No, not really. It might be partially because it is late at night here, but I am utterly confused by your three files.
None of them seem to "Worksheet_Change" I created for you, and the buttons and worksheets are in a different language.
So I am having a very difficult time following along here.
I think we are talking back and forth, but not really communicating well.

Note that there is a Questions in Other Languages forum here, where you can post questions in your native language, if that might help.
Someone who speaks and understand your language may be able to understand the workbook better.

If you want to pursue this further in this thread, then I have this proposition for you.
Strip your problem done to the "bare bones". What I mean is try to make a real simple example, with minimal data and minimal code, that shows the problem you are having.
Let's try getting rid of all extra stuff and focus just on this issue.
Can you create a super simple example that shows your problem?

In order to show the problem i am having regarding this thread those three files are needed, for context, otherwise you wouldn't be able to inspect the problem that i'm having. That's the simple example I can get to you, because i cutted it to 1 department instead of the 10 I have.

I can, and i'm more than willing to, change names of the things, so they are user friendly and you can understand them.

Regarding "worksheet change" it is on sheet "Pendentes" from "TAPOIO SP3.xlsm" as "PrivateSub myVlookup"

Regarding "Question In other Languages" i'm aware of that forum, tho i navigated there and didn't find threads in portuguese.

So i can fully detail what is happening, on a well structured presentation of the problem if that suits you changing the portuguese names to english names, so the code gets user friendly.

I Hope we can keep together, let me know if so! Or at least give one more try on this.

Thank you Joe!