Trying to reference a date using a specific criteria.

jjwapp

New Member
Joined
Jul 14, 2019
Messages
1
Looking for a formula that allows me to find the date for the first value given >0.
For example, P/N 70032 want to find 7/29/2019 date.



P/N
Past Due
7/8/2019
7/15/2019
7/22/2019
7/29/2019
70025
30
0
0
0
0
70028
0
0
0
0
0
70032
0
0
0
0
172
70042
0
0
0
0
0

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
I can't think of a combination of formulas to do this other than an inflexible list of nested IF() functions combined with OFFSET()

I have written a small UDF (user defined function) that will do it for you. It also has a restriction (as a UDF can't use the 'CurrentRegion' property of a range): The heading of the partnumber column has to be "P/N". If not then you will have to modify that in the macro.

Press the Alt-F8 key in Excel and choose 'New'. This will open the VBA editor. In the editor window (right empty pane) paste the function below.
Save your file as a macro enabled workbook.

Now you can use the function by typing into a cell: =getdate4pos( then click with the mouse on the partnumber and close the bracket. You will notice that ecxel shows the function as you type.

Code:
Function GetDate4Pos(rCell As Range) As Date
'///////////////////////////////////////////
'// Function to return date in top row of //
'// range with partnumbers in left column.//
'// Date returned is for first non-zero   //
'// cell to right of partnumber.          //
'// Top left cell of range should have    //
'// text "P/N"                            //
'// ------------------------------------- //
'// Usage: =GetDate4Pos(A5) where A5 will //
'// be a cell with partnumber in the left //
'// column.                               //
'///////////////////////////////////////////

    Dim vAr As Variant
    Dim i As Long, c As Long, j As Long, UB1 As Long, UB2 As Long
    Dim sPN As String
    
    Application.Volatile True
    sPN = rCell.Text
    For i = 1 To rCell.Row - 1
        If rCell.Offset(-i, 0) = "P/N" Then
            UB1 = rCell.Row - i
            Exit For
        End If
    Next i
    c = 1
    Do While Len(rCell.Offset(-i, c))
        c = c + 1
    Loop
    UB2 = c - 1
    
    For c = 1 To UB2 + 1
        If rCell.Offset(0, c) > 0 Then
            Exit For
        End If
    Next c
     GetDate4Pos = rCell.Offset(-i, c)
    
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,102,759
Messages
5,488,683
Members
407,651
Latest member
Halosty

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top