Solved

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

Posted on 2011-09-19
7
169 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 41

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 41

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 41

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 41

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 41

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now