Solved

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

Posted on 2011-09-19
7
173 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
  • 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 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
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!

 
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

Independent Software Vendors: 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

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

685 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