Log In
Register
Home
Active Member
October 12, 2020
Offline
Hi There,
Countinue to the topic of "Automating and Emailing Pivot Table Reports" and been post by Philip,
I did download the file excel and ths for sharing the excel file.
But I m having problem in step “Create an email and attach the PDF”. The fomular of Vlookup does not perform as usual. When I press Run the VBA , the fomular start to pick random email from database, not matching value. I did change fomular a lit bit and format data cell in number order (for easy matching) but problem is not solved. Could you help me out of this, I am appreciate your help
Below is fomular I am having trouble with. I also attach my file in this forum.
.Display
.To = WorksheetFunction.VLookup(Range(“B1”).Value, Worksheets(“Managers”).Range(“Managers!A2:B15”), 2, True)
See Answer
October 5, 2010
Offline
Hi Huy,
The issue is that VLOOKUP requires the lookup values to be sorted in ascending order. The table you were using for lookup had the values in random order.
Sort the ma column in ascending order and it should all work fine.
regards
Phil
Answers Post
Active Member
October 12, 2020
Offline
Hi Philips,
Ths for your excellent answer. I got the problem now. The Vlookup in VBA performs a bit different from excel it used to be.
Is it possible for vlookup to looking value in random order as my case?
Best regards,
Huy Dang
October 5, 2010
Offline
Hi,
VLOOKUP will only work on data that is sorted.
If you have XLOOKUP you can use that on unsorted data though
WorksheetFunction.XLookup( Range("e3").Value, Worksheets("Managers").Range ("Managers[ma]"), Worksheets("Managers").Range( "Managers[Email]"))
Regards
Phil
Active Member
October 12, 2020
Offline
Hi Philip
The code is works perfectly. Ths very much for your helping
1 Guest(s)
