?
Solved

Code that searches from right to left for the first cell with a value greater than zero

Posted on 2011-09-19
7
Medium Priority
?
176 Views
Last Modified: 2012-06-21
I need some code that will define the variable "y" not as the first cell to the left of the last column, but rather the first cell to the left with a value greater than zero.

I was using this until I discovered that the searched for cell sometimes contained a zero:

Set y = ActiveWorkbook.ActiveSheet.Cells(x, 256).End(xlToLeft)

If there's no simple way to accomplish my goal with a simple modification of the above, then something of this sort might work but of course I need it to start looking left from the last column and I need it to find the first value that is NOT zero:

Set y = Rows("2:2").Find(What:="0", After:=Cells(2, 256), LookIn:=xlValues, searchdirection:=xlToLeft)
I thought that this might search backward from Column IV but of course it doesn't.

Thanks,
John
0
Comment
Question by:gabrielPennyback
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36564680
Don't think you can do that with the find method.  However, if you're willing to leverage a cell to evaluate an outcome in your workbook, you can use this:

Sub nonZeroFromRight()
Dim wkb As Workbook
Dim sht As Worksheet
Dim r As Range
Dim col As Long

    Set sht = ActiveSheet
    Set r = sht.Range("A1")
    r.FormulaArray = "=MAX(COLUMN(2:2)*(2:2<>0))"
    Debug.Print r.Value
   
End Sub
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36564740
I was just trying to find out if you can use EVALUATE with array functions - not so far...

More correctly written to your task:

  sht = activesheet
  set x = sht.range("A1") 'or someplace you're not using

  x.formulaarray  = "=MAX(COLUMN(2:2)*(2:2<>0))"
  set r = sht.cells(2,x.value) 'this would give you the range address of the rightmost non-zero value

Cheers,

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 36565078
I guess it WAS just sitting in front of me - for some reason I kept talking myself out from testing it (its an array function that in a cell would require CTRL-SHIFT-ENTER, but when in an Evaluate statement, nothing special needed!).  Well, what do you know... you CAN do it with the evaluate command!:

Set Y = ActiveWorkbook.ActiveSheet.Cells(2, Evaluate("MAX(COLUMN(2:2)*(2:2<>0))")) 'get range of farmost right cell <> 0 in Row 2

Example attached.

Cheers,

Dave
farmostRightCell-r1.xlsm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36571098
This is great, Dave, thanks. It took me a while but I finally figured out how to do it with a variable. In the process I was pleased to learn that you don't need an actual range to evaluate, a string will do.  

Brad (byundt) would have my head for going back and forth between two books 1,378 times, but I timed it and it actually only took 14 seconds, which is pretty much a miracle on the slow network I have here at work. :-)

Thanks!

John

Sub GetFlightLegs()
Application.ScreenUpdating = False
[AF1] = Now()
For Each cel In Range("Z3:Z1380")
    'cel.Select
    Dim Rw As Long, rng 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))
    y.Copy
    ThisWorkbook.Activate
    If IsNumeric(y) Then Cells(Rw, 26) = y
Next
[AG1] = Now()
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 36571440
That's right - notice the quotation marks :)

Otherwise, you could do [max(whatever)] but if you're doing a string to build the formula then it has to be Evaluate("max(whatever)")

Cheers,

Dave
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 36590515
Not quite sure what you mean by "you could do [max(whatever)]"  Could you rewrite my code to use that instead of what I have?

Thanks again for such a great answer.

John
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36590524
I don't think there's anything wrong with your code.  What I was saying is you can use the [] evauate brackets unless you need to build a string to put changing parameters in like you did, then you use Evaluate(string) as you have - and as I proposed as a solution...

E.g.,

Evaluate("MAX(COLUMN(2:2)*(2:2<>0))")

is the same thing as:

[MAX(COLUMN(2:2)*(2:2<>0))]

but when you need to change the column, you build a string, then you have to use the Evaluate command with built up strings, you can't use the brackets []..

I always try to use Evaluate anyway, and I avoid [] brackets on everything...

Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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