asked on

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

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

dlmille

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

dlmille

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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

dlmille

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

John Carney

ASKER

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

dlmille

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