[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Macro that finds first non-zero value to the left of the last column fails if the cel in the last column has a value

Posted on 2011-10-11
14
Medium Priority
?
366 Views
Last Modified: 2012-05-12
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) = z
Next

Open in new window

0
Comment
Question by:gabrielPennyback
  • 8
  • 6
14 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36953359
The function:

{=MAX(COLUMN(1:1)*(1:1))} - ctrl-SHIFT-ENTER to confirm array function

Will return the last column, if the last column has a value.  Try it...

What do you mean >> gets stuck on that cell and won't look to the left.  ??

Anyway, the answer to your question:

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


is to use the MATCH function

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

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


Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36953361
Type-o in my first two lines of last post, should read:

The function:

{=MAX(COLUMN(1:1)*(1:1<>0))} - ctrl-SHIFT-ENTER to confirm array function
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36953371
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
Concerto Cloud for Software Providers & ISVs

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!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36956922
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.

Thanks,
John
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36957149
Hi Dave, here are a couple of sample workbooks.

Thanks,
John
Sample-FH-LookupCX.xlsm
Sample-Copy-of-t-FH.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36957713
Ok - please advise what I should be looking at.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36957743
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.

What is the outcome you're looking for?

Dave
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36958006
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."

I tried things like<<| str = "MAX(COLUMN(" & Rw & ":" & Rw & ")*(" & Rw & ":" & Rw & "<>0))"

Thanks,
John
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36959122
MATCH(TRUE," & Rw & ":" & Rw & "<>0,0)

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

So, I recommend revising your code to:

str = "MAX(COLUMN(" & Rw & ":" & Rw & ")*(" & Rw & ":" & Rw & "<>0)*(" & Rw & ":" & Rw & "< 40000))"


Cheers!

Dave

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36959509
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!

John
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36959530
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 :-)


John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36959582
>>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!

Dave

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36959628
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))))

And thanks for the MATCH(TRUE approach as well!

:-) John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36959635
Sorry - ISNUMERIC is a vba function.

Excel formula:

*(NOT(ISNUMBER(4:4)))
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

834 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