[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 - find last non-0 cell in column

Posted on 2012-08-28
4
Medium Priority
?
450 Views
Last Modified: 2012-08-28
Hi.  I wrote a process in Excel to load data and show graphs that has been running for over a year.  I realized when it didn't work yesterday that I didn't handle if there is a bad data value, which rarely happens but it could happen again.  

The current formula in the cell, which is below, works if there are no data issues in the text file.
=ADDRESS(MATCH(0,J:J,0),COLUMN(J1),4)

I am trying to figure out how to change the formula to look at the J column and return the cell only if that cell is followed by at least 5 cells with 0.00 in them.

I've included an image of how the data looks in column J.  Normally the value in J2 is a non-zero value.
J column data

My old formula would return J1, based on this data, but the new formula or function should return J7 (because 5 cells follow that have 0.00).

Thanks for any help you can offer.

Regards,
Alexis
0
Comment
Question by:alexisbr
  • 3
4 Comments
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38342223
Put this function in a module and then

=FollowedBy5Zeros()

Function FollowedBy5Zeros() As String
    Dim lngLastRow As Long
    Dim lngIndex As Long
    
    lngLastRow = Range("J65536").End(xlUp).Row
    For lngIndex = 1 To lngLastRow
        If Range("J" & lngIndex + 1) = 0 And _
           Range("J" & lngIndex + 2) = 0 And _
           Range("J" & lngIndex + 3) = 0 And _
           Range("J" & lngIndex + 4) = 0 And _
           Range("J" & lngIndex + 5) = 0 Then
           FollowedBy5Zeros = "J" & lngIndex
           Exit Function
        End If
    Next
    
    
End Function

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342244
I edited the above.
0
 

Author Comment

by:alexisbr
ID: 38342517
Thank you very much.  That works perfectly.  I understand what you wrote but I wouldn't have thought to do it that way.  

Alexis
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38342834
You're welcome and I'm glad I was able to help. My MVP is for VB6 and so I always think about code:)

Marty - MVP 2009 to 2012
0

Featured Post

Industry Leaders: 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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 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