VBA: Confirming correct formatting following a blank cell

[overbet]

Just a side note to moderator(s) and its not my intention to sound like I am complaining just providing a paid users perspective on my experience and offering constructive feedback that hopefully leads to higher revenue for your site and a better experience for your paid users.

In my mind I am a customer paying for assistance and I have currently been waiting for a response to my post over 3 hours and counting now. I would be better off just posting in the general forums where I am likely get multiple responses much sooner from very knowledgeable folks, perhaps not experts, but knowledgeable all the same. In my past experiences I have always gotten satisfactory help in general forums. I chose to try the pay option because a solution is urgent and important to me and therefore worth paying for. I figured if it didnt work out it was worth a try and if it did work out great because then I know I would use the service very frequently.

I would like to post my question in the general forums now due to lack of activity in the paid section. I looked for a way to delete the commercial version post of my question so as to not have double post, but I did not see an option to do so. Please feel free to remove the paid post question if this is a problem.



Anyway I still need a solution so if someone could help me out I would greatly appreciate it and I would be willing to donate my last 10 credit points to them in exchange for a working solution if that is possible to do.



I am unable to upload a sample, but below hopefully is clear to understand. "MAIN COLUMN HEADER A" would be cell A1, "SUB HEADER 1" would be cell A2, "MAIN COLUMN HEADER B" would be cell B1 in this example:


MAIN COLUMN HEADER A MAIN COLUMN HEADER B MAIN COLUMN HEADER C
SUB HEADER 1
sample data
sample data

SUB HEADER 2
sample data

SUB HEADER 3
sample data
sample data
sample data
sample data
sample data
sample data

SUB HEADER 4
sample data
sample data
sample data
sample data
sample data

SUB HEADER 5
sample data
sample data
<<< This cell would be an error-should be blank cell followed by “SUB HEADER 6”
sample data
sample data
sample data
sample data


Column A is the only place I need to look for the error and the only place it should look because the other cells in the sheet which are not in column A would generate false positives. This error checking method I devised will only function properly if ran in column A data only.

I need to be alerted in some way (no preference how I am alerted) so that I can manually correct the error. Alert can work like Find Next or fill the cells black for example.

Important that my data is not be altered, my cells cannot shift. I just want to be shown some way where the errors are located or some way to easily identify them so I can manually change them.

Primary header data in cell A1 so begin looking in cell A2:A for blank cell then look down to A3 next then A4 next then A5 and so on.

Next cell directly below a blank cell found should be all CAPS. If not all CAPS then it is an error and alert where that error exists so I can inspect it and change it manually.

There are no double blank cells in column A. I have a macro that has removed them so the first double blank cells in column A will be the end of the sheet.

There is about 100k rows of data and it has taken me a couple hours to manually check 15k rows so I am hoping to get a solution because I already caught myself missing an error because its such a mind numbing task.

If a solution requires vba I am familiar with how to implement it and do not require any special assistance with that aspect. I just dont know how to write the code or formulas, but I have experience creating modules, inserted code, added references and buttons and so forth. Thank you

[cytop]

So you're basically looking for cells in Col A where the text is all Caps and is not preceded by a blank cell directly above...?

[overbet]

Yes that is correct.

[cytop]

Please Login or Register  to view this content.

Just cobbled together and not terribly efficient but it'll check 1,000,000 cells in about 15 seconds. All Caps cells without a blank cell above are coloured red and a msgbox showing the total number of cells in error will be displayed when it completes.

It assumes the Active Sheet is to be checked.

Note it formats cells and removes formatting at the start of the procedure - just was the simplest thing to do as storing the row number, or a reference to the cells, would be pointless if you subsequently edit the sheet - the references will no longer be correct.

[overbet]

Hey that will work I just tested it in a copy workbook. I do have color formatting in the cells in A that is a vital component. Would it be possible to keep my formatting or no?

If not this is still a huge help and I am grateful for your assistance. Is there a way I can send you my remaining credits for your effort and as a thank you?

[cytop]

That's the problem with forums - unless you say exactly what to do/not do then assumptions are made.

Added a Yes/No Message. Click 'Yes' to just count the number of errors, clicking 'No' will select the first error found and exit the procedure.

Please Login or Register  to view this content.

[jeffreybrown]

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...

[overbet]

Yes, that is my mistake. I imagine most help request post are made by people whose minds arent geared toward software/code writing speccing and we dont consider all the variables.

I really appreciate your help and effort. You have allowed me to be able to spend the afternoon with my son instead of in front of this screen. Is there some way I can repay your efforts? I do have these credits if you want them though I am unsure how to go about transferring them. Thank you

[cytop]

I do have these credits...

No thanks - I have no use for them.

However, I was going to mention the thread title but JB got there before me - the forums likes descriptive titles that summarise your problem as it aids others when searching for solutions. It would be appreciated if you did edit it.

[alansidman]

Just a side note to moderator(s) and its not my intention to sound like I am complaining just providing a paid users perspective on my experience and offering constructive feedback that hopefully leads to higher revenue for your site and a better experience for your paid users.

In my mind I am a customer paying for assistance and I have currently been waiting for a response to my post over 3 hours and counting now.

It is important to note that everyone on this forum is a volunteer and are not getting paid, with the exception of the Commerical Services on a case by case issue.
Even then, we are a world wide forum working in all time zones. Not everyone is on the forum all the time. Patience is virtue when it comes to getting an answer on the forum. Additionally, you have posted on a day that is typically not a workday for many who visit this site. Activity on weekends tends to be lower than during weekdays. I hope you understand this when and if you post again. In the meantime, you should contact one of the administrators about closing your post in the CS and make your funds available to you.

Welcome to the forum and we look forward to your future postings.

[overbet]

I tried to edit my title and deleted my entire post. I am having a very hard time with this site's gui. I do not want to leave the thread like this because I received help that might be useful to someone else in the future, but I dont know how to go about repairing the damage. Please advise on how to recover my post and edit my title. Thank you

[jeffreybrown]

Please give me a good title here and I will update the thread.

[6StringJazzer]

I would like to post my question in the general forums now due to lack of activity in the paid section.

I am posting this as a member and not a Moderator.

First, you opened your Commercial Services thread at 10:38 AM today. At 10:59 AM, a mere 21 minutes later, I locked your thread and posted a solution and asked a few questions to get you to define your problem better. I have no idea what you mean by "a lack of activity."

Second, let me explain how this forum works. There is no pool of professional Excel consultants on call to respond immediately to your Commercial Services post. We are not a consulting service. We are just people on the Internet. When senior members (Gurus and above) have availability, they check that subforum, and if the problem is well-defined, the points are adequate, and they have the time, they will "lock" your thread to solve it, as I did. For your 10 points, the person who solves it gets a whopping $7.50. I don't know what your expectations are, but 21 minutes is a very fast response time here.

My solution was a good start and met your sketchy requirements. I would have been happy to have developed a more comprehensive solution, including VBA, if you had been more prescriptive about what your data looks like and what behavior you wanted. You are very lucky you found members here in the free forum willing to go back and forth and get you want it turns out you needed.

I see that two other Moderators have already posted to this thread, so I will leave Moderator mode off.