# Trying to reference a date using a specific criteria.

#### jjwapp

##### New Member
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>

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### sijpie

##### Well-known Member
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``````

1,102,665
Messages
5,488,179
Members
407,630
Latest member
Mehezabin

### 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...