Compare which column has first non-blank cell

[Alphonse68]

Hello everyone,

I have a spreadsheet that has two columns populated with formulas in each cell starting at H3 and I3, which compare different data each, and only display an error code.
I need a macro which finds the first cell with an error code (non-blank) in each column, compares the row number of the two cells, and goes to the cell with the lowest row number in whichever column it appears (H or I). If the first non-blank in each column happens on the same row, it should go the one in H.

At the risk of overstating, here's an example:
If first non-blank cell in H is at H23, and if first non-blank cell in I is at I17. Compare 23 to 17 and go to I17.
If they both happen at, say, H34 and I34, go to H34.

So basically:
find first non-blank in H
find first non-blank in I
if row # in H <= row # in I
goto H#
else
goto I#

This looks so simple, but...

Cheers,
Alphonse

[TMS]

Suggest you post a sample workbook to test with.

(Though I may not be able to access it on my laptop)

Regards, TMS

[Alphonse68]

Hello TM,
Thanks for answering.
Here is a simple test file. It is not complete, but it serves the purpose.
Cheers.

[TMS]

Try this:

Please Login or Register  to view this content.

Regards, TMS

[Alphonse68]

WOW! Works like a charm!! Thank you so very much.

There is some code in it that I would have never thought of, even if I knew they existed, short of divine intervention, like the definition of the awf, Transpose, and UBound. Thank you, also, for the explanations; they sure helped in understanding most of how it works.

Most of all, thank you for your time in writing the routine. May good things always come your way.

Cheers.
Alphonse

[TMS]

You're welcome. Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Alphonse68]

Hi TMS,
I know we already closed this post, but I forgot to ask, is the line Option Explicit need to go with the code when I add it to the rest of my macros? I ask because I want to make it part of a set of routines bunched together to do about 5 things at once at the push of a button.
Also, what does it mean or do?

Cheers,
Alphonse

[jolivanes]

By Mr. Tom Urtis

Before you go without the Option Explicit statement, take a moment to carefully consider that decision.

The term "Option Explicit" means that the VBA code author must define the names of all the variables being used or referred to in the code. Not doing so is called "implicit variable declaration", where you can name a variable without explicitly defining it.

Why should you explicitly declare variables?

Option Explicit at the top of a module forces every variable to be declared. There are several reasons why this is a good idea to maintain:

- Spelling errors are identified.
- Your variables can be properly declared by you, not Excel, to have the appropriate memory and system resources assigned to accommodate them. Excel will assign Variant as the variable default type for undeclared variables, the most inefficient use of resources.
- Confusion is avoided if a variable is the same name as a property or method.

Consider the following code:

Please Login or Register  to view this content.

Without Option Explicit to guard against spelling errors, cell A1 will never have a value returned into it because of the spelling inconsistency between FindData (line 2) and FineData (line 3). The macro will always exit because FineData will always be thought of as empty.

This kind of mistake might be noticeable with a macro this size, but if it is part of a larger procedure these mistakes are hard to identify. Why take the chance?

Keeping Option Explicit as the default statement is highly recommended; it forces better code writing practice, and helps avoid coding errors.

[TMS]

@jolivanes: and your point is?

[jolivanes]

@TMShucks.
In reply to the question: "Also, what does it mean or do?"

[TMS]

@jolivanes: ah, my apologies. I completely missed post #7 where that question was asked.

So, a very thorough and helpful explanation ... thank you ... I was just bemused as to what had prompted your response (and I certainly wouldn't have had anything so detailed to hand).

Thanks, TMS

[jolivanes]

No apologies needed TMShucks.
Have a great weekend.

John

[Alphonse68]

Hello TMS and John,
Thanks for the info. I learned a lot from this post. I gathered that the Option Explicit line is good for writing code, but it does not need to included once the coding is completed. I have a series of tasks that I grouped into one Sub, I copied the one from this post in the middle of it without the OE line, and it works just fine. Thanks again to both of you. Take care.
Cheers,
Alphonse.