# Custom function

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:23 PM GMT

## Question

Remember? you have provide me with a superb macro for one of my specific problem where i need to look up the time duration of a b# from a table by looking at the start time and if the number appears twice then it will look within a five minute range and that it also has a GMT feature where i could mention the time zone?

well please see the attachment for recalling the scenery o. i am also pasting the macro for your convenience as follows:

Option Explicit Public Function FindMatch(rTime As Range, rBnum As Range, rTime1 As Range, rBnum1 As Range, GMT As Double) Dim diff As Double, k As Long, i As Long, j As Long Dim vt, vb, t, n, r As Range, ddiff As Double, mindiff As Double Dim GMT1 As Double

vt = rTime1.Value vb = rBnum1.Value t = rTime n = rBnum j = -10 GMT1 = TimeSerial(GMT, 0, 0) diff = CDbl(TimeValue("00:05:00")) k = rBnum1.Row - 1 mindiff = diff For i = LBound(vb, 1) To UBound(vb, 1) If n = vb(i, 1) Then

``` ddiff = Abs((vt(i, 1) - GMT1) - t)
If ddiff < diff And ddiff <= mindiff Then
mindiff = ddiff
j = i
End If
```

End If Next If j >= 0 Then Set r = rBnum1(j).Offset(0, 1)

```  FindMatch = r.Value
```

Else

```  FindMatch = 0
```

End If End Function

well, it worked extremely well. I just need a few modification, can i please include the date clause as well please. the current function is:

FindMatch(rTime As Range, rBnum As Range, rTime1 As Range, rBnum1 As Range, GMT As Double)

so the modified function will be as follows:

FindMatch(rDate As Range,rTime As Range, rBnum As Range, rDate1 As Range, rTime1 As Range, rBnum1 As Range, GMT As Double)

I am also facing problem for numbers occurring withing the 5 minutes zone, i am badly in need of a modification where it will fetch the closest match if duplicates number appears withing the 5 minute zone. in the attachment you will see that in the cell F5 & F8 the duration is showing wrong for B# 1614169833 & 1196025335 respectively.

i can modify the code by reducing the time range but in many case that causes problem. So it would be really great if it can intelligently detect the number and brings the closest match that full fills all the criteria, that is the date, the time, the bnumber, 5 minute range and finally the closest.

i am sending a sample copy at your mail as well.

Thanks and humble Hirok/Sami