• Status: Solved
• Priority: Medium
• Security: Public
• Views: 184

# 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
0
John Carney
• 5
• 2
1 Solution

Commented:
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

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

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

Commented:
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

Reliability Business Tools Analyst IIAuthor Commented:
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
``````
0

Commented:
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

Reliability Business Tools Analyst IIAuthor Commented:
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

Commented:
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
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.