Posts by Persononfire
-
-
Hello,
Sorry for the late bump. The hurricane put a damper on a lot of what I was doing, time-wise. I submitted a new thread a few days ago because I wasn't familiar with the rules on bumping on these forums, but you can ignore that.
Everything is working good, except when there is no information for a certain category. When it reaches a sheet that has an empty category, the macro crashes.
-
Part 2:Electric Boogaloo
I apologize for posting another thread for the same question. Around the time that I was trying to get it solved, the hurricane struck and put us out of commission for a solid week, then an overload of work prevented me from following up on it for a while. Apologies especially to Mumps who was the primary person helping me. I'm reposting because the thread is a month old, and I feel like my attempt at being flexible with a solution instead made the problem too vague & ambiguous, and therefore unnecessarily complicated. Some additional requirements have also been brought up to me, so I can note them in this new thread.
Old thread link: https://www.ozgrid.com/forum/forum/he…on-trigger-word
Attached is the sample file from the previous thread. It is not exactly like the original, because the original is full of sensitive data. Also, making it more like the original would be a lot of redundant work, because
Basic script needs:
- I need to grab the entire row, not specific columns. This is because sometimes notes get put to the right side, and some categories are longer than others.
- For each category of information, the first row is the category name, second row is generic header, and the third row onwards is
- The script must stop when cell A is empty. This is due to the fact that there are hidden rows that are empty before the "Total" row. The cell with the word "Total" in it also moves around, so I cannot use that as a constant in the script.
- Some sheets will not have any rows of information, but will always have the categories and the header below it.
- Really, the goal is "Check each sheet. If row X has the word 'Category 2', start grabbing every row below it until it finds a row where cell A is empty. Repeat on next sheet, for however many sheets there are. Skip one row for the header." The simpler the better.
Differences between the sample & the actual file:- I put three categories, but there are actually four, There is considerably more information above category 1, specifically in rows 1-4, but if they add more rows with more information, then I won't know it. If they create a new category, then I'd like to be flexible in incorporating that as well.
- The category names are much different than simply 'category 1'. For example, the first one is actually "Rejects (Active or Cancelled Contracts that did not make it to the system)".
Again, I apologize for the repeat post. The simpler I can make solving this for mumps or whoever else, the better, please let me know if you need additional information. -
I'm sorry. The last file I gave you is the closest thing to the original I could provide, without dipping into sensitive information. The only things misrepresented is the sheet count, the variances in how many lines of info each page has, and the fact that some sheets do not have any lines of information for all four categories (but they still have the category names & the headers in the sheet).
As for the category 3 lines though, my last file does have the additional data in the last two rows. I had to double check to make sure I wasn't crazy.
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.PNG","data-attachmentid":1222097}[/ATTACH]
-
Looks like it's on the right track. I moved all of my file's sheets into your file and changed their table names to "Category 1-4", and tried it. The formula pulls info from the first two sheets, then crashes when it encounters a sheet that doesn't have any data in it, which I forgot might be the case sometimes. It will have the tables and headers & everything, but it has no info for that month.
Also, it doesn't pull the last two columns of data from category 3, which is slightly longer than the others.
-
Attached is something slightly closer to the actual file, with everything in it changed to just say info or the like.
I should also note that each time I receive this file, there are a different number of worksheets that it extracts from, so it would have to be all-inclusive of whatever is in the file.This month's file had 52 sheets; I'm sorry to say I did not want to redact information out of all of them, haha.
-
So far so good, but a few problems, partially due to mishaps on how I presented the sample file.
• When I try to capture the info for category 3, it doesn't capture the last two columns. I changed the "last" row in the formula from L to N, but then when I tried to do category 2 with that change, it uh...Well, I attached the results. I think this has to do with the location of the word Total differing. Also, in my actual company file, the word "total" is not located in the last column, but actually the second to last column, because below all the numbers it has a subtotal formula.
• When I tried it with my actual company file, I got a run-time error 91: Object variable or with block variable not set, with the debug referencing the line "If Not fnd Is Nothing And fnd.Offset(2) <> "" Then". Not certain why.I apologize if the issue was caused by my setup of the sample file. The actual file I'll be running this formula has categories 1 and 2 running to column I, and category 3 goes up to column K. The word "total" appears in column G for all three categories, if this helps any. I created a 4th sheet that portrays what my original file looks like a little better.
There is one additional thing I'd like to request: For ease of access for whoever is using the file, is it possible to make the choices for categories either as a dropdown box or as three buttons? The actual category names are slightly lengthy, so there would be a large margin of error for typing them in, I fear.
-
Does the sheet where you want to compile the data already exist or does it need to be created? I assume that the category you want to grab will vary. Will it always be only three categories? As you select a category and transfer it to the "compile" sheet, do you want to keep the previous category or categories that were previously added to the "compile" sheet ?
• I can create a sheet if necessary, or the script can create one on its own, that part is flexible.
• Yes, the category I grab will vary, but I don't mind changing the category name within the script.
• Yes, there's always only three categories.
• When I compile the information to one area from a specific category, I'm going to take that information immediately and plug it in somewhere else entirely, so it doesn't matter whether running the script for a new category deletes the information I compiled previously. If I had preference, then I would like the information removed before I compile data for a different category. With that said, it doesn't have to present it in a clean manner. It could literally just put the rows and the sheet that it comes from in the compiled sheet, and that's it. -
I only need one category, but yes, I want the information combined to one sheet. It can either be a sheet I prepare in advance, or a blank new sheet, doesn't matter.
Sheet1 has six lines of information in category 2, Sheet2 has three, and Sheet3 doesn't have anything. Basically I want to compile all nine lines of information in one sheet, with "Sheet 1/2/3" to the left of the information in column A to signify where it's from.
If the script made one compiled sheet for each category, that would work too, but I am just wary about capturing the extra useless information at the bottom. There's a lot more useless rows of stuff at the bottom than my test file that I don't care for.
-
Hello,
I apologize in advance if this has been asked before. I've found similar questions & results but based on different restrictions.
I receive a file from another company that has multiple sheets with sets of information that are broken down into three categories per sheet. I need a way to compile all of the lines of information into one sheet, but only lines that are under a certain category, and to put the sheet name that the line is from in column A. Shoddy example file attached. So essentially, from every sheet, I would need to grab all the lines of information under "Category 2" up until, but not including, the header row and the row at the bottom of the category that has a "Total", slap it together in a single sheet, and add the sheet name that it originates from to the left of the information. This would mean that "Category 2" would be a trigger word for the macro to start grabbing.
I'm unable to turn any of the information into actual tables or named ranges, as the files are sent from elsewhere, unfortunately.
Thank you kindly,