•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Complicated matrix with buggy VBA. Don't know what went wrong, much less how to fix it :(
Our take
Are you struggling with a complicated Excel matrix that has gone awry due to buggy VBA? You're not alone. Many professionals face challenges when trying to harness the power of spreadsheets for tracking and coordinating tasks. This guide will help you troubleshoot and optimize your VBA code, ensuring your matrix effectively visualizes training progress and future classes. Discover practical solutions to transform your spreadsheet from a frustrating mess into a reliable tool that impresses your commissioners. Let’s get started on restoring your once-proud creation!
Hi! Hope this is acceptable here. I'm looking for someone to troubleshoot some VBAs. I think this is the right terminology? I work for a small county government. Part of my duties are to track and coordinate training for our elected officials. With 5 of them, plus myself, it can be a lot. I'm a visual person, so I created this matrix. I want it to do a few things when I eventually throw it up on a projector for them: show what they've taken to date, show what classes are most efficient to take, and demonstrate future changes by toggling a class on or off.
I'm pretty good with technology, but Excel is not in my wheelhouse. Full transparency, I ChatGPTed my way to this point. I told Chatty what I wanted this spreadsheet to do, and it walked me through the VBAs.
The top part works fine for the primary tracks. It's the crossover hours section that's faulty now. I want it to turn classes they've taken blue. Once they've satisfied that third crossover class for a specific track, I want it to essentially disqualify all the others by turning them purple.
For what it's worth, it was working like a gem (a bit slow, but working nonetheless) before I had to go back in and add some rows. Now it's a mess.
Is there anyone who can help me here? I was so proud of this baby, and it was actually a useful tool! I was so close to being able to use it with commissioners in a group setting. Now, I don't know even what to fix, much less how. I tried ChatGPT again, but no luck. I'm at the point I need a human! Happy to chat if you need more info or the file itself.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- how did you improve your workplace's legacy vba macros?I recently transitioned to a non-clinical role in a public health care system. part of the on-boarding was a 12 page, 20 step tutorial on how to 'do the macros'. The workflow simplified is: - Get source data from EHR/BI - Open the excel online (microsoft 365) "Daily Review" workbook in the desktop ms excel. (hopes and prayers it doesn't crash) - copy data (columns of patient ID, demographics, medications... you get the idea) from EHR, paste into this Daily Review - run macro (click a button) which cleans, filters, applies conditional formatting i think - save - go back to excel online and resume editing there. The VBA code was created (not sure if it was written coz it has no documentation) by a colleague who is on extended mat leave. I can see a lot of 'modules'. Can't tell which is active. There are probably lots of historical decisions. The daily review file with its many many sheets is saved in multiple locations in case newcomers like me or others break it by accident. I am told we can't change anything like move a column closer to the beginning coz well we can't. I don't know VBA but could probably figure it out if I watch a tutorial on it. I am linux user and know basics of C, python and make good use of my claude code with the pro subscription but never really worked with spreadsheets. I am wondering if anyone was in a similar situation and how you managed it. Is moving to office scripts (typescript) a viable alternative? Any other life improving tips would be appreciate it. Or maybe I should just give up and focus the energy elsewhere? submitted by /u/Neat-Badger-5939 [link] [comments]
- My conditional formatting and data validation keeps breaking when it wasn't previously.I have a daily report I have to review and I built a workbook to make the process more digestible. Here is the basic structure of my workbook/worflow: Each day I extract a report to a CSV file. I save it in a designated folder and replace the previous days report. Sheet 1 has a power query mapped to where I save to report I refresh the data which updates the table. I apply some filters/delete items/sort rows (I deleted via deleting table rows). I have a macro button that sends all of my filtered and sorted data to my Daily_Maint table on sheet 2. Sheet 2 Daily_Maint table I have additional columns for manual data. One column on the end for comments (which has no issues). Second to last column is a review status column which has data validation to a list on a separate sheet (Completed, outstanding, system maintenance, circle back). I have (or had) conditional formatting for the list options to color code the list options (green,red, yellow, orange respectively). In another column (one which lists different users) I had conditional formatting to change the color for 2 specific users. In a column that lists client number, I have the cells formatted to insert letters before the numbers. Applied to the whole table I have conduit for atting which it looks at the column with client numbers (aka RM #'s) and when the number is diff from the cell above it, I have formatted a border across the whole table. (This visually separates groups of maintenance by customer). Once I have completed my review, I make a copy of the sheet and rename it to the date of the report. I delete the rows in the Daily_Maint table and do it all again the next day. Recently our drives were remapped and I had to remap my power query. When that happened my macro broke... Which didn't make sense because the macro and power query shouldn't talk to each other. As if by magic the macro started working again. What I'm struggling with: Data validation keeps breaking. This was rarely an issue before the remapping. But now it breaks every day and I can't figure out why. Conditional formatting for color coding keeps breaking. Origioally O tried applying it to =$O:$O which worked well until it didn't. I tried applying it to an absurdly large range like =$O$6:$O$20000 but after I delete rows it changes the applied to formula to what ever number was after the range I deleted. (Ex $O$500:$O$19500) Similar situation with the condition l formatting for the usernames. Tried =$M:$M and this worked really well for months... Until it stopped. Conditional formatting for the line to visually separate groups by customer. My original formula was =$F7<>$F6 applied to the full sheet. This would break every day but it was an easy enough fix I was fine living with. But after talking to AI I tried =INDEX($F:$F,ROW())<>INDEX($F:$F,ROW()-1). I thought it was a permanent fix because I tested it several times. But when I actually use it... It duplicates/breaks/still needs fixing every day. I've tried applying the formatting to "This Table", "This worksheet", and "Current Selection" and nothing is working. What am I doing wrong? Would it be more stable if I built everything on the worksheet and not in a table? I think I'm too close to it at this point to have any perspective and I don't have that much excel experience, most of my knowledge I've gained through building this spreadsheet. I appreciate any advice. submitted by /u/jazzlikebridge42 [link] [comments]
- Make this convoluted system easier?Basic Excel user here....nothing more than basic formulas. I inherited a convoluted system and am trying to see if there is a better way of doing things than the current manual process. I have a spreadsheet that tracks about 40 different rooms/cabins and which ones are granted access to services (eg: internet, TV, premium TV). Currently we using a spreadsheet with a main tab for all cabins, and then a separate tab for each room/cabin. The main tab basically just tracks the room/cabin, service, and start and stop dates. The info from the main tab are then transferred to the individual room/cabin tab. The tabs are then color coded for whether they should have a service currently turned on/of (green/red) Right now it's all done manually - basically we log in and check each tab to see what needs to be done for the week.......so, open it on Monday, check each tab to see if any need to be turned on.....check on Friday to see what needs to be turned off. Then we go into our other system to actually activate/de-activate services....and change tab color as needed. Is there a way to automate the checking of each tab to see if it falls within the current date (or within a few days) and automatically change the tab color? At least that way we could have a quick and easy visual on which cabins should have service. I'm envisioning something that basically works like this: open the spreadsheet, run something (script, formula, vba ???) that will check each tab - is there anything listed for the current date range (maybe it even prompts for a date range ??), if so turn that tab green. Is there a tab that is currently green but doesn't have anything for the current date? if so, then turn that tab red. Hopefully that all makes sense. I know it seems a bit convoluted and it can be a bit time consuming, especially when multiple people are accessing the sheet and may or may not (usually may not) actually follow the instructions. submitted by /u/GateComfortable1693 [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#financial modeling with spreadsheets#rows.com#Excel compatibility#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#VBA#Excel#matrix#training#county government