Trying to reference a date using a specific criteria.
Results 1 to 2 of 2

Thread: Trying to reference a date using a specific criteria.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Canada
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to reference a date using a specific criteria.

    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

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to reference a date using a specific criteria.

    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
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •