Custom function

QuestionEdit

This Sami/Hirok again badly in need of your help.

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 know, that you are the only person who can help me out. Please please please help me.

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

Thanks and humble Hirok/Sami

AnswerEdit

Hirok/Sami,

I received your email. I will see what I can do. It may take a day or two as I have a lot going on right now and this should take quite a bit of time to get back up to speed on the macro and then determine how it needs to be modified.

Hopefully you have highlighted and example of where the 5 minute limit does not work and what the correct answer should be and why. (if it is just the closest match when multiple rows are within 5 minutes, then I can probably handle that).

I will respond to your email when I have updated the function.