• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Excel 2010 - find last non-0 cell in column

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
alexisbr
Asked:
alexisbr
  • 3
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
I edited the above.
0
 
alexisbrAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now