find closest date/time value

[hrm1220]

I collect information from "Start" sheet that has the date and time, which is stored in the varList (but is seems to store it as text). In my "Report" sheet I have the minimum time (I get this from the "Start" sheet) as the starting point. Then add 30 seconds to each row.

What I'm trying to do is have VBA find the closest value on my "Report" with the vaule in the "Start" sheet

So in "Report" sheet, my start time is 1/1/2009 00:00:41 AM. Then in cell "A2" I would have 1/1/2009 00:01:11 AM, cell "A3" I would have 1/1/2009 00:01:41 AM and so forth.

In my "Start" sheet I have a time that is 1/1/2009 00:00:59 AM. So I would want the data from this row to be put into Row 2 of the "Report" sheet, since it's the closest value w/o going over.

I've put in the code and I can't seem to get my match to work since it seems to look for the text value and the excel file is a formula and hence has excel's number value. So, even though you see the date and time it has the cell as excel's date/time number (39855.5018634259)

Dim timeCol, LCol, SLCol, SeqCol, RecCol, ChCol, chendrow
Dim endrow, Lendrow, Lfltr, chfltr, LNum, intopt1Count, endcol, FirstRow, startreport
ReDim varList(4, 0)
Set startreport = ActiveSheet
'

    Range("B65536").Select
    Selection.End(xlUp).Select
    endrow = ActiveCell.Row
    Range("B4").Select
    endcol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

'''finds all the Ls for this report
Set Lfltr = Range(Cells(FirstRow, LCol), Cells(endrow, LCol))
 Range(Lfltr.Address).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        (Lfltr.Address), CopyToRange:=Range("IV1"), Unique:=True
    Range("IV2").Select
    LNum = ActiveCell.Value
    Selection.End(xlDown).Select
    Lendrow = ActiveCell.Row
Set chfltr = Range(Cells(FirstRow, ChCol), Cells(endrow, ChCol))

Range(chfltr.Address).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        (chfltr.Address), CopyToRange:=Range("IU1"), Unique:=True
    Range("IU65536").Select
    Selection.End(xlUp).Select
    chendrow = ActiveCell.Row
    Range(Cells(2, 255), Cells(chendrow, 255)).Select
    Selection.Copy
    Sheets("Report").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

startreport.Select
'''this sorts the data into L, Rec, Wafer sL and Wafer Sequence
    Cells(FirstRow + 1, 1).Select
    ActiveCell.Formula = "=" & Cells(ActiveCell.Row, LCol).Address(rowabsolute:=False) _
    & "&" & Cells(ActiveCell.Row, RecCol).Address(rowabsolute:=False) & "&" & _
    Cells(ActiveCell.Row, SLCol).Address(rowabsolute:=False) & "&" & _
    Cells(ActiveCell.Row, SeqCol).Address(rowabsolute:=False)
    Selection.Copy
    Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(endrow, ActiveCell.Column)).Select
    ActiveSheet.Paste
    Calculate
    
    Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(endrow, endcol)).Select
     Selection.Sort Key1:=Cells(5, LCol), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
        Range("B4").Select
    Selection.AutoFilter Field:=LCol, Criteria1:="=" & LNum, Operator:=xlAnd
   

'''this collects that data to put in Sequence order
For i = 5 To endrow
    If Rows(i).EntireRow.Hidden = False Then
    
          If intopt1Count = 0 Then
                varList(0, intopt1Count) = Cells(i, SLCol).Value ' number
                varList(1, intopt1Count) = Cells(i, SeqCol).Value ‘Order
                varList(2, intopt1Count) = Cells(i, RecCol).Value '
                varList(3, intopt1Count) = Cells(i, ChCol).Value  '
                varList(4, intopt1Count) = Cells(i, timeCol).Value  'Time
                intopt1Count = intopt1Count + 1
           Else
                ReDim Preserve varList(4, UBound(varList, 2) + 1)
                varList(0, intopt1Count) = Cells(i, SLCol).Value ' number
                varList(1, intopt1Count) = Cells(i, SeqCol).Value 'Order
                varList(2, intopt1Count) = Cells(i, RecCol).Value '
                varList(3, intopt1Count) = Cells(i, ChCol).Value  '
                If Cells(i, timeCol).Value > varList(4, intopt1Count - 1) And _
                Cells(i, SLCol).Value = varList(0, intopt1Count - 1) Then
                varList(4, intopt1Count) = Cells(i, timeCol).Value - varList(4, intopt1Count - 1) 'Time
                Else: varList(4, intopt1Count) = Cells(i, timeCol).Value 'Time
                End If
                intopt1Count = intopt1Count + 1
           End If
         'i = i + 1
    End If
Next i
Sheets("Report").Select
Range("B2").Select
j = 2
For x = 0 To intopt1Count - 1
Cells.Find(varList(3, x)).Activate
chCol = ActiveCell.Column
timesplit = Split(varList(4, x), "/")
timestart = Mid(timesplit(2), 8, 6)
datesplit = Split(varList(4, x), ":")

[b]timematch = Application.Match(datesplit(0) & timestart, Range("A1:A10000"), 1)[/b]
    Cells(j, chCol) = varList(0, x)
    'ells(j, 2) = varList(1, x)
    'Cells(j, 3) = varList(2, x)
    'Cells(j, 4) = varList(3, x)
    'Cells(j, 5) = varList(4, x)
   j = j + 1
Next x

Any help would be greatly appreciated.

 

[SkipVought]

Date and Time are NUMBERS. Do not use STRING functions. Use Date functions.

The INTEGER part is DATE. The fractional part is TIME. It's ALL math, not string manipulation.

timematch = Application.Match([b]CDbl(YourDateTimeValue)[/b], Range("A1:A10000"), 1)

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hrm1220]

I've tried that and it gives me a type mismatch. It maybe that the source data has the date/time and the PM/AM after it. Could this be the reason why?

So I used the split to take out the AM/PM but it still gives me a type mismatch error.

 

[hrm1220]

I still get error 2042

 

[hrm1220]

ok. thx for pointing me in the right direction Skip... I just needed to declare my datevalue.

Cells.Find(varList(3, x)).Activate
chCol = ActiveCell.Column
timesplit = Split(varList(4, x), "/")
timestart = Mid(timesplit(2), 8, 6)
datesplit = Split(varList(4, x), ":")

timematch = Application.Match(datesplit(0) & timestart, Range("A1:A10000"), 1)

Thanks again for your help

to

[blue]Dim Datevalue as Double
Cells.Find(varList(3, x)).Activate
chCol = ActiveCell.Column
datevalue = [b]CDbl[/b](varList(3, x))

timematch = Application.Match(datevalue, Range("A1:A10000"), 1)[/blue]

 

[SkipVought]

FYI faq68-5827.

So I used the split to take out the AM/PM

Let me repeat...

Dates have NOTHING to do with TEXT! Date/Time are NUMBERS. What you see is a Display Value. The underlying value is a NUMBER. Using string functions on a display, indicates that you do not understand what you're working with. Please read and understand the FAQ I posted.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]