• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP Another workbook with multiple sheets

haha ofc -.-!

awesome man, thank you so much for your help man. I will donate some as promised once I am home, there is no way to donate to you particularly is there?
 
Hey man,

Sorry to bother you again but I am getting a new error when implementing the code on my full scale project. I am getting:

"Run time error '13': Type Mismatch"

one of the source documents has like 10 tabs all with 1000+ lines so I am assuming its getting stuck on that one can you please help?
 
This error depends on

• which line it occurs

• variables values

• data values

It's easy to find out for the one in front of screen
via debug mode in VBE : see codeline, values in Locals window, …
​
 
Hi Marc,

the debugger is taking me to the line that says:

For R = 2 To UBound(VA)

Therefore this leads me to think that it might be due to the fact that not all the shipment #'s on the master can be found on the trackers. If that is the case then its fine since I did a random check from the values the macro picked up and they all matched!
 

So if you check value of variable VA ?!

That should mean an empty worksheet, no data, just check it !
​
 
Marc,

Would you happen to have an email ID I can contact you with? I think it would be easier if I showed you with the real attachments but I didn't want it to publically post the documents -.-
 
Hi Marc,

After much diagnosing it seems that whenever the code encounters the next file in the folder it bugs out on that same line claiming type mismatch even though I have verified that every sheet contains values on the same row therefore VA shouldn't be empty!
 

It is easy to check when error occurs in debug mode
via Locals window what contains VA variable …

I guess there is nothin' within worksheet except headers so no data !
​
 
Hey Marc,

Since I am not very good at understanding these things I have attached a print screen of the locals screen along with the bugged line and maybe you can give me some tips from there if sending this to you via email is not possible... Sorry -.-
 

Attachments

  • Error.jpg
    Error.jpg
    254.1 KB · Views: 8
Within your pic I see as I wrote VA variable does not contains any data
but just a string "Loreal invoice Value" ‼
Compare with any sample workbook …

Just check worksheets of this OVERSEAS.xlsx workbook !
​
 
At beginning you clear all data in master worksheet !
Range("A2:E65536").Clear​

So after VA variable is empty 'cause Columns(6) is yet empty
as you can see yourself in debug mode via Locals window
following code hitting F8 key …

My initial code works only with data in column F within Master worksheet.
​
 
Ok, I have tried to remove the range clear option and even after removing it and re-running the macro I am still getting some values omitted (same as before adding that line).

Also, I don't understand why column 6 would be empty if the range clear only goes through column 1-5(A:E)
 
Yes you're right ‼ I was not well awaked …

In fact your Master worksheet has duplicates Shipment#
as for example rows #104 & 142 …

But :​
Marc L said:
Is possible duplicate references between workbooks / worksheets ?
Click to expand...
RPMdragon said:
no, all the shipments# are unique values and are not repeated in any other sheet/workbook
Click to expand...
So my code works only for unique values, not duplicates !

You may try Range.Find method instead of a Dictionary …

Or using Excel MATCH worksheet function …
​
 
Hey Marc,

You are 100% correct! I didn't even noticed those slippery duplicates were there! ok Great, now both codes are functioning perfectly! Now only solving the issue from the other thread xD Thank you
 

Duplicates are not an issue if they are expected !

Using for example MATCH function in VBA
can do the job with your post #38 attachment …
​
 
You must log in or register to reply here.
Back
Top