• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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

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
John Carney
Asked:
John Carney
  • 8
  • 6
1 Solution
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Dave, here are a couple of sample workbooks.

Thanks,
John
Sample-FH-LookupCX.xlsm
Sample-Copy-of-t-FH.xls
0
 
dlmilleCommented:
Ok - please advise what I should be looking at.

Dave
0
 
dlmilleCommented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
dlmilleCommented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
dlmilleCommented:
>>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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
dlmilleCommented:
Sorry - ISNUMERIC is a vba function.

Excel formula:

*(NOT(ISNUMBER(4:4)))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now