In the code below "MAX(COLUMN(" & Rw & ":" & Rw & ")*(" & Rw & ":" & Rw & "<>0))" finds the first non-zero value to the left of the last column in the active worksheet of the external workbook. It works great until it hits a row with a value in the last column, in which case it gets stuck on that cell and won't look to the left. Hence it returns a zero value for z.

How do I get it to look and find the first non-zero value in that row?

Thanks,
John

For Each cel In Range("Z170:Z1388") cel.Select Dim Rw As Long, y As Range, z As Long, str As String Rw = cel.Row str = "MAX(COLUMN(" & Rw & ":" & Rw & ")*(" & Rw & ":" & Rw & "<>0))" Workbooks([M2].Value).Activate Set y = ActiveWorkbook.ActiveSheet.Cells(Rw, Evaluate(str)) If y.Column < 21 Then z = 0 Else z = (y + y.Offset(0, -2) + y.Offset(0, -4) + y.Offset(0, -6) + y.Offset(0, -8) + y.Offset(0, -10)) End If ThisWorkbook.Activate If IsNumeric(y) Then Cells(Rw, 26) = zNext

Egads - I'm so sorry, I can't seem to type correctly.

The MATCH function you want, for the first non-zero would be:

{=MATCH(TRUE,1:1<>0,0)}- ctrl-Shift-Enter to confirm - or, in your code:

str = "MATCH(TRUE," & Rw & ":" & Rw & "<>0,0)"

I guess I got dizzy parsing your string in my head. I did the right thing and tested it in the immediate window, so the assignment to str should be correct, now...

Dave

0

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Hi David, thanks for the posts. The first one bugged right away as you suggested. The second one returned all zeros. I wish I could post the workbooks but there's so much client confidential info in them that I can't. If I have time this morning, I'll try to dummy up two small sample books.

In the meantime, is there some way to modify the original code so that it starts its search from the next to last column? Column 255 in this case since external workbook is an XLS.

Ok - I ran the flight legs routine. I stepped through the code. Your current code is finding the 147 in column EP. That's the farthest right non-zero item.

Hi Dave, thank you for pursuing this so thoroughly, I really appreciate it. Yes it does find the 147 in [EP3]. But because of the date in [IV4], it doesn't find the 130 in [EP4].

If I use str = "MATCH(TRUE," & Rw & ":" & Rw & "<>0,0)", I get a zero in both [Z3] and [Z4] in the calling workbook ("Sample-FH-LookupCX.xlsm).

My understanding of how the function works is still a little murky, but my sense of it is that it's looking for the first non-zero value working backwards from, say, [IV4], INCLUDING [IV4] itself. If I've got that right, I keep wondering if there's a way to modify the original code so that it starts looking leftward from [IU4] instead."

Should be finding the leftmost value non zero, which is apparently column 1 in your sample-copy-of-t-FH.xls sheet. I would assume this is also what you're not looking for.

The way to get at this, rather than coding in VBA first (as a learning exercise) is to try the formula raw, in the sheet. I'm doing that now...

Assumption... Given that IV4 is a valid date, which is a numeric item somewhere in the range > 40,000, we can use that as an exclusionary criteria.

Hence, the farthestmost right column, not having a date, and not zero is:

=MAX(COLUMN(4:4)*(4:4<>0)*(4:4 < 40000)) - ctrl-shift-enter to confirm

I test that in the spreadsheet, and PRESTO! I get column 146

Awesome, Dave, thanks! So I see that this functions like a SUMPRODUCT formula. Which I guess means that I can add other conditions if I need to.

What exactly is the logic of the function? At first (even 31st) glance it seemed to me that the formula was looking (in Row Rw) for the column containing the highest number (MAX), excluding zeros and numbers higher than 40,000. When in actuality it's looking for the highest column containing a number that is greater than zero but less than 40,000?

So assuming that I finally get it, would this be the way to find the highest row in Column D (with the same constraints)?

=MAX(ROW(4:4)*(4:4<>0)*(4:4 < 40000))

In any event, thanks for a) a great solution and b) a little education!

Of course I just realized that it's looking for any kind of value at all, words as well as numbers. Which is fine in this case. But how would I modify the function so that it excludes non-numerics?

You don't need to answer this, but if it's easy please let me know. Meanwhile I'll see if I can google up an answer :-)

>>wesome, Dave, thanks! So I see that this functions like a SUMPRODUCT formula. Which I guess means that I can add other conditions if I need to. YES - it is an array function, just like SUMPRODUCT in some respects (though requires CTRL-SHIFT-ENTER to work in a spreadsheet)

You can add many conditions in much the same way - the comparison values between the * resolve to TRUE/FALSE, then the entire set of comparisons are resolved to TRUE/FALSE, and that's what's multiplied against the VALUE parameter (in this case ROW() - is multiplied by TRUE for every column that's non-zero - and the correct COLUMN is then revealed via the MAX function - where all values are zero except the row multipied by TRUE.

>>What exactly is the logic of the function? At first (even 31st) glance it seemed to me that the formula was looking (in Row Rw) for the column containing the highest number (MAX), excluding zeros and numbers higher than 40,000. When in actuality it's looking for the highest column containing a number that is greater than zero but less than 40,000?

Maybe I just explained it. The function is looking for the highest column NUMBER that meets the specification - recall MAX(COLUMN()*(true/false conditions)) - so MAX is not evaluating any values inside the cells, but the column number.

>>So assuming that I finally get it, would this be the way to find the highest row in Column D (with the same constraints)?
>>=MAX(ROW(4:4)*(4:4<>0)*(4:4 < 40000))

Close -

=MAX(ROW(D:D)*(D:D<>0)*(D:D < 40000)) would give you the highest row in column D that's non-zero and not > = 40000 (e.g., for our purposes, not a date)

>In any event, thanks for a) a great solution and b) a little education!

You're quite welcome - I learned alot about this feature thanks to your question and am using both max column/row quite a bit, lately - even on a couple occassions when it was overkill and I realized I didn't need it :)

>>But how would I modify the function so that it excludes non-numerics?

try the constraint *(ISNUMERIC(4:4)) ' and remember there is a level of processing intensity associated with this (having lots of these in a spreadsheet), but using them in a macro I think the time savings in coding outweighs alot and the impact is not as noticeable as having hundreds (for example) of these formulas in a spreadsheet.

and to exclude numerics? try *(NOT(ISNUMERIC(4:4)))

There's a lot of ways to skin the cat, but the fundamental structure - finding max column/row will remain the same.

PS - jot down the MATCH(TRUE approach to find the FIRST occurrance as you may need that, one day!

Thanks, Dave. *(NOT(ISNUMERIC(4:4))) returned a name error but you have educated me to the point where I could think my way through to this, which does work: =MAX(COLUMN(4:4)*(4:4<>0)*(ISNUMBER((4:4))))

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are â€¦

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦