Solved

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

Posted on 2011-09-19
175 Views
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
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
• 5
• 2

LVL 42

Expert Comment

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

ID: 36564740
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

LVL 42

Accepted Solution

dlmille earned 500 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

LVL 1

Author Comment

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
``````
0

LVL 42

Expert Comment

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

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

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

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month6 days, 7 hours left to enroll