This is where things are going to start to get tricky. In the example I went through, I selected A, just a single capital letter A as from the drop-down menu. So what I'm going to do, is I'm going to start in cell B1, and what we're doing is we're searching for where the leftmost single-digit in this example. So A infer cell B1, A also for C2, but then when we go from C1 to D1, it's no longer A. So that's going to be an important transition. In general I'm going to be searching through these cells to see which ones have the leftmost same string as that of which we selected from the drop-down menu. Category name here is what we selected. That's the text or the string that we selected from the drop-down menu in the combo box. So in our example in the first example I went through, category name was just capital A. So we would iterate through here and see where we go from A to something else and that's an important transition because that means we're at the end of all the A's. So I'm going to go through here and I'm going to say, does this cell have the same first letter as the next cell? It's not just the first letter because what if you had homework in there? So you're going to have to use maybe the leftmost n number of digits of that string. But in this case, we're going to say, is the first string of this label different from the cell next to it? That's false. But then we go here, the leftmost single string of this is different from the one on the right. So this C to D is an important transition because that's where we're going to want to insert a column. In order to detect if two adjacent cells have the first string part, I put this line in here that says, if left, the active cell.offset, so that's our current cell. In this case that's always going to be B1 as our active cell, if left active cell offset comma length, now the category name in my example was just A, but if it were homework, then we'd be doing the length of homework which is eight. So we would check to see if the left most eight digits of the active cell is equal to, now on the right we have the category name. So that would be homework or in this case is just A. If that's true, that means that particular cell is in the category that the user selected, then I'm going to use this variable n in category, that's the number in the category, and I'm going to bump that up by one. When we do that, it's important to always reset that to zero. So this n in category is basically counting the number of items in each category. This next line is quite complicated. So spend some time trying to understand this. So if left of the active cell is equal to category names similar to what I did up here, so if we find a match, we're in the same category as what those users select in the drop-down menu, so if that's true if we're in the same category. This underscore by the way just brings it down to the next line, so you can continue it without it going all the way over to the right. If the cell to the right of it, so this just says if the cell to the right of it is not equal to the category name, so the leftmost digits of that cell not equal to the category name. So what I'm doing here is this and this have the same category beginning, the the prefix A. But then when we go to the next one, these two adjacent cells do not and that's important because wherever we have a transition, we want to make note of that because again that's where we're going to add in a column. So that's what this first part does, but we also have an "or" because we can also go to the end because if the category we're trying to add is at the very end, we're not going to have anything adjacent to it. So I put the "or" in there such that we can have an E is empty, or if the cell next to it, and that's what we're doing here, is empty, if either one of those is true and the current cell has the category name we're looking for, then we need to make note of that. We need to mark that. That's the important place where we're going to add the column. I also forgot to dim in in category as an integer. So I've just done that. So once we find the transition from the prefix that we're looking for to the next category, we're going to mark that and we're going to be at a certain I. What I'm going to do is I'm going to put this Find Address which I also need to dim as a string. Find address is going to be the address, so if I do dot address, that's going to be the address of where we have the transition. In our example, find address, if I selected A from the drop-down menu, Find Address would actually be D1. Because that's where we have the transition from A to B. What I'm going to do here is use the split function. Find address is going to be dollar sign D, dollar sign one. What I'm going to do is use the split function that's going to split our address based up on the dollar sign. So really this is going to give us an array. The first element of find array is going to be D and then the second element is going to be one. I also dimmed find array up here as a variant. What we're really interested in is getting the letter because that will tell us the column letter of where we need to insert a column, so I'm going to have this call letter string is equal to Find array one. So the first position of the array that's generated with the split function. So find array one and I need to dim call letter, that's deemed as a string. Now what we need to do is select the entire column of that call letter. So column D and co-ordinate that with the column and the D again. So columns D colon D, select. So that statement selects is basically the right-clicking and selecting that entire column. Now I just recorded a macro for when I go into here and do right-click, delete, I just recorded a quick macro and the code for that is this. So selection.insert shift to the right. So that will insert a new column and shift everything to the right. Finally, we just need to place the next category into that new column that's opened up. So if I use range, colletter, so there'll be D In this example, D1 range D1 equals the category name. So that's A plus the number in category. That's why counted the category number up here earlier, that's going to be two if we have two categories already and we're just going to add one. So that'll leave us with A3 for this example. Just some formatting stuff I'm just going to at the very end just instead of having the entire column selected, I'm just going to select A1 just as formatting preference and I'm going to replace this message box we had from earlier and I'm just going to say a column for category name and category plus one. So that'll be A3 for this example, has been added to the files. But before we display that message box, it's going to go into the loop, it's going to open up the second file, do the same exact thing, it's going to go into third, fourth, fifth and so on, until all the files in that directory have been modified and the great item has been added. Again, just make sure at the very end you have unload userform1 instead of userform1.hide. Now I didn't show you this earlier, but I have my sub here add entire column. We're going call populate combo box and then we're going to show userform1. The populate combo box, we just have one combo box. I'm just adding items A, B, and making the text capital A.
