Hi there ExcelForum community!
It's my first post, don't be harsh if I do something incorrectly, I am still new to VBA
I am trying to create a macro to help out with the daily workload at work, but due to various office policy, I cannot visit this at work (most websites are blocked at work ), neither can I download any files at work from the desktop.
Therefore I can't really copy paste the stuff I already have, unless I'm to re-write it from scracth at home completly. Truth to be told, I don't have much because this macro is too challenging for a newb like myself, so I am counting on some help
Description:
We have two files.
First file is called "DailyWorkload", consisting of 1 sheet only, called "Sheet1".
Inside we have a variable number of rows (between 50 to 300 usually, but let's keep it defined as variable, to keep the code flexible in case there is a bigger workload).
First row is column headers. There are 4 columns. Column D is "CountryVendor"
Second file is called "Exceptions List". It consists of multiple sheets, however the data which will be used is located specifically in sheet "VendorsList".
On VendorsList sheet, we have a list of vendors. Vendor names are concatenated with the country, because same vendors might be present in more than one country and have different exceptions to follow in every country). The "CountryVendor" name is in column G.
Please note that new exceptions can be added to this file, therefore the range with the CountryVendor values to be compared to, should also be flexible, starting from row 2 until last row.
Goal of the macro:
I want the macro to check every vendor ("countryVendor") in the DailyWorkload and see if there is an existing exception for this "countryVendor" in the Exceptions List file. If yes, the description of the exception should be copied from Exceptions List and pasted into Daily Workload file. If the exception for the "CountryVendor" doesn't exist, then delete the row. Which means that at the end of the macro, in the Daily Workload file we should only see the rows with "CountryVendors" that have an existing exceptions.
How I wanted it to work, so far according to my plan:
Define range1 in DailyWorkload, in column D, as cells from row 2 until last row (CountryVendors list for that particular day).
Define range2 in ExceptionsFile (sheet VendorsList) from cell G2 until last row (so the list of all CountryVendor exceptions input in the file)
Then, starting from the end of the range1, going upwards, for every cell in range1, find if the value is present (matches/equals) anywhere in range2.
If No (meaning this country/vendor combination has no exceptions), then delete the row in DailyWorkload file.
If yes (there is an existing excpetion written down for this country/vendor combiation), then copy the description of this exception (under column E) from the "Exception List" (sheet" VendorsList") file, into the DailyWorkload file under column F
Now what I am stuck at (or what I suck at), is the proper definition of those two ranges, because they are in different workbooks and I can't seem to define it properly and loop it correctly. S
Additional question (more of a theoretical question on how you'd solve it): what if a vendor has two exceptions in the same country? Obviously those exceptions can be just combined in one huge cell with lots of text. But What if someone, by accident, enters second exception for the same CountryVendor combination, or just enters a duplicate? How would the macro behave in such situation?
Is there any smart way?
What I thought about, is that instead of copy-pasting the description from one book into the other, we'd just copy the UnqiueID of the Exception (columnA), into column F on the DailyWorkload workbook, in such way that it should be more than 1 value . Like let's say IrelandGoogleInc has 3 exceptions, UniqueIDs: 5, 210,211. So then macroo, when searching for "IrelandGoogleInc" value in the range2, would find it 3 times and would past this in column F: 5;210;211. The employee would know which Exceptions to check for this vendor.
Anyway, I know it's a heck of a big ask, but if anyone can help me out, I'd be really happy.
I tried to describe it as best I could, I do realise that without any basic tables and examples it might look a bit abstract, but if you need to ask any more specifics, I will be happy to provide them
Bookmarks