Solved

Macro that finds last non-zero value in a row fails if the last non zero value is below Column R

Posted on 2011-09-30
3
240 Views
Last Modified: 2012-08-13
I don't really understand how the meat of this macro works but it does work up to a point (or should I say down to a point). If the last non-zero value in Row 4 is in Y4, it selects Y4. If the last non-zero value is in R4, it selects R4. But if the last non-zero value is in Q4 or P4, etc., it continues to select R4.  

Can you see anything here that would cause that? Or is there a better way to accomplish my goal anyway? Row 4 consists of formulas, most of which return nothing. Otherwise of course I could use a simple [IV4].end(xlToLeft).

Thanks,
John
Sub FindLastNonZero()
Dim leftmost As Range
Dim str As String
str = "MAX(COLUMN(4:4)*(4:4<>0))"
Set leftmost = Cells(4, Evaluate(str))
leftmost.Select
Exit Sub

Open in new window

0
Comment
Question by:gabrielPennyback
3 Comments
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 150 total points
ID: 36895432
First as to how the function works.  The column(4:4) part returns a list from 1 to 16384.  It's all the column number for ever cell in row 4.  So, it doesn't matter what the number is in this part.  Column(1:1) works just as well.

The (4:4<>0) creates a list of TRUE and FALSE values for every cell in row 4, TRUE if the cell <> zero and FALSE if the cell = zero.

These two arrays are multiplied together and the results is a list with a bunch of zeros and some numbers.  The zeros are the results of a FALSE being multiplied with a column number.  The numbers are the result of a TRUE being multiplied with a column number.  This is because of the principle of coercion.  Excel "coerces" TRUE's and FALSE's into 1's and 0's when performing arithmetic operations.

Lastly, the MAX function finds the largest value in the list of zeros and other numbers.  The largest number is the column number that did not get killed with a FALSE.  This number happens to be column containing the last non-zero value.

Now, as to why it won't work for you.  I can't help with that.  It works just fine for me both in VBA and as an array formula, see attached workbook.  Maybe you have some corruption going on.  Have you tried the code in a new sample workbook?

Kyle
Q-27375035-RevA.xlsm
0
 
LVL 41

Accepted Solution

by:
dlmille earned 350 total points
ID: 36896003
>>Row 4 consists of formulas, most of which return nothing

Are you implying that Row 4 has formulas which return the "" empty string as nothing, or 0 as nothing.  If its returning the empty string, then a valid result would be the rightmost column being the empty string if there's nothing else to the right of it, because the empty string does not equal zero.

Looking at your other posts, I'm assuming that Excel 2007+ is your version.  Some of my spreadsheet examples use IFERROR() function, so if you're using 2003, let me know and I can give you a stand-in for that.

Comments:  Is it possible the formula result for R4 is "" empty string, and S-Y4 are show zero when this happens?
That could be why you're getting a failure.  See EXAMPLE #1 in the attached, where formulas return numbers and also empty strings.  Note the RightMost column identified is demonstrated with this formula on sheet 1.

Now, if you want the farmost right address with empty string evaluated as a non option (as with the zero), then we have to do a bit more.  Originally, when we worked on this, the assumption was that there'd always be a number as a result of the formula, but I'm stretching here and empty string is one way you'd be getting erroneous results, so I'm going on that assumption.

The formula we'd need, which can be used as your new general purpose "farmost right" formula would be:
 
if testing on row 16, which I am in EXAMPLE #2.  Note the test for non-empty string in addition to non-zero

MAX(COLUMN(16:16)*(16:16<>0)*(16:16<>"")) 'use CTRL-SHIFT-ENTER to confirm array formula

One could have just as easily tested for ABS(16:16)>0 in which case, any non-zero number is being tested for, as opposed to testing for the negative.  But, this would not work if you wanted the farmost right cell on data that contained string/text output.

See example #3 in the attached for this example, using this formula:
MAX(COLUMN(22:22)*(IFERROR(ABS(22:22),0)>0)) 'use CTRL-SHIFT-ENTER to confirm array formula

For all these examples, keep hitting F9 for the random generated output to show different results, and you can see the successful pickup on the farmost right, based on that formula's definition of farmost right!

I updated your macro around the two negative tests (arbitrarily, and I would have picked that to start, and it also supports strings in the cells, in addition to numbers.  The ABS alternative came out as another alternative just to keep the creative juices flowing and to help with understanding - but it will only work if your range has numeric output...

Here's the code, and I have a button on that code (SEE TEST MACRO TAB), keep hitting it (I put a calc in to generate output...) so you can see it select the correct cell, as well!

Note, one flaw in the code is if all columns have no whole number (or character) results. E.g., no 1, or no "ABC" exists in any cells (this gives results for whole number and string results, so you get the farmost nonzero,nonempty cell.  I've corrected for that, looking for a result of 0 and ensuring you get column 1 as the result - you can change this as you see fit.

 
Sub FindLastNonZero()
Dim leftmost As Range
Dim str As String
Dim resultCol As Long

    Application.Calculate 'only inserted for demo purposes
    str = "MAX(COLUMN(4:4)*(4:4<>0)*(4:4<>""""))"
    resultCol = Evaluate(str)
    
    If resultCol = 0 Then 'avoid getting an error
        Set leftmost = Cells(4, 1) 'just make it column A - or do what you need to do with this case
    Else
        Set leftmost = Cells(4, Evaluate(str))
    End If
    
    leftmost.Select
    
End Sub

Open in new window


See attached,

Enjoy!

Dave

farmostRightCell-r2.xlsm
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36905104
Thank you, Dave. Your first statement about blank vs zero got me to look more closely at the formulas and then I noticed that my formula changed at Row R. Everything from R up had my original formula: =COUNTA(INDIRECT(R10&201)). But at some point I got gratuitously "correct" and changed it to: =IF(COUNTA(D11:D211)= 0,"",COUNTA(D11:D211)). Even though the original was already producing blanks instead of zeros. Fortunately I dragged it over only as far as Column R or I never would have figured it out even with your post!

And kqerb, great education, very easy to understand. So I want to award you some points too.

Thanks,
John
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now