Macro to lookup and highlight between different sheets

[RenanCip]

Hello guys!
First of all, I'd like to say I've tried to search for similiar problems on this forum, as its not very specific, but I couldn't find for a solution so far.

I work for a company which receives thousands of orders every month. We keep the monthly order data on a specific worksheet, and we also receive daily
worksheets issued by the credit card companies, informing what they owe us, due to those orders.

What I need is to crosscheck them, to see if the credit card companies are actually paying us for every single order that goes through, with the same value

So im attaching the files to make it clearer. What I am trying to do is:

Part 1) - Compare sheets via a unique number (located on column L on the monthly sheet, and column B after line 35 on the daily worksheet). While looking up, the macro should check the values on column H from the monthly sheet with the values on column F of the daily sheet. If they are the same, highlight the unique number or write an "ok" or something like that on the monthly sheet.

Part 2) - Since I have 30 daily sheets every month, I wonder if would be possible for the macro to open every one of them to check one by one


I know its nothing really super complex but it takes some time to implement so any help will be really appreciated
Thanks in advance



Example (Daily).xlsxExample (Monthly).xls

[RenanCip]

Bump no response

[natefarm]

Sorry, I'm confused. The NSU #s in your daily and monthly sheets don't match up, and if one of them did match, there are still multiple NSU #s in the daily sheet. Which one do the values above (in rows 14-31) go with? And what about the duplicate values (like 67.65) in column F? And is the dollar value the only thing to tie them together? What if you found a matching dollar value, but it wasn't actually the same transaction? How would you know?

[RenanCip]

The NSUs donīt match up because I removed most of the lines just to send the sheets as an example, on the original files I've got about 9000 rows on the monthly sheet and about 300 on the daily sheet (x30 days = 9000).

The NSU is the unique number which should be the parameter of search. Yes, there can be several transactions with the same value, what identifies it as the same transaction is the NSU. So what I'm trying to achieve is like:

Macro selects the NSU from the first row (column L) on the monthly sheet, then looks for a match on the daily sheet (column B, starts a little further down), if its there AND corresponds to the same dollar value on both sheets, which should always be the same (column G for both cases), then that row is highlighted on the monthly sheet, and proceeds to the next row.

As for the second part of my help request, I'd like to know its possible for the macro to open up every excel file located inside certain folder, so after the macro would end the search on the first daily sheet, it would keep running on the others.

Hope I could explain myself a little better now, thank you!

[RenanCip]

Oh and about the values on rows above 31, they are another kind of data which is irrelevant for my purpose. Since they will never have a number similar to a NSU, I donīt think they would interfere with the expected results. I thought it would just be better to ignore it instead of trying to create an exception for the macro not to lookup for those numbers.

[natefarm]

I think part of what threw me off was you had first said the amount was in column F of the daily sheet, which it is in the upper section, but not in the lower section where the NSU numbers are. If it's in column G as you say now, then it makes more sense.

[RenanCip]

Im trying to improve this code I've found on another thread. It highlights successfully, but only between two sheets on the same workbook. Also, it is highlighting the NSU row on the daily sheet, not the monthly. I know I'm having trouble with some basic stuff hehe. I would also need to put the i=i+1 for the iteration somewhere

Please Login or Register  to view this content.

[RenanCip]

Also there isnt a line that checks the equal values on the columns G

[natefarm]

Give this a try. Adjust the path and filename as needed.

Please Login or Register  to view this content.

[RenanCip]

Thank you so much for your time nate, and sorry for my late replies, because I only have access to my sheets at work

Do you have any idea why I might be getting a error 1004 asking me if the file has been moved, renamed or deleted? It's strange because I have changed for the right path and the right file prefix (all the daily sheets start with the word in portuguese "Resumo", followed by some numbers and letters). When I run the macro, the message actually shows correctly the full name of the first file (ResumoVendasCredito_2014-12-17_144539601.xls), so if it returns the correct sheet that should be opened, why wouldn't it do so?

Please Login or Register  to view this content.

PS: This isnt probably of any help but when I debug, it yellow lines the

Please Login or Register  to view this content.

[natefarm]

It's also strange because it's trying to open a file that the code found. When it hit's the debug, hover your mouse over the filename and see what it says.