Help with a Time function or ... Something

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 31 May 2008 15:04 GMT

Can some help me with a time funtion or idea. I have a workbook that I am
running a comparison between Sheet1 and Sheet2. I am comparing sheet1 columnP
with sheet2 columnP. If this matches I need to compare sheet1 colum F with
sheet2 column G. Colum F And Colum G are Time stamps.

I would like to be able to say if Colum F And Colum G are within 15 minutes
+ or -
then highlight that row.

Here is my code and i am already finding matches for ColumnP
For looper = 2 To lastToCheckRow
Set columnPcellPointer = xlWs.Cells(looper, 16)
For looper2 = 2 To lastToCheckRow2
Set columnPcellPointer2 = xlWs2.Cells(looper2, 16)
If columnPcellPointer = columnPcellPointer2 Then
If columnPcellPointer.Offset(0, -10).Value =
columnPcellPointer2.Offset(0, -10).Value Then
xlWs.Range("A" &
columnPcellPointer.Row).EntireRow.Interior.ColorIndex = 6
End If
End If
Next looper2
Next looper

Reply to this Message

Gary''s Student - 31 May 2008 15:38 GMT

Here is a function that will return true if two times are within 15 minutes
of each other:

Function within15(r1 As Range, r2 As Range) As Boolean
within15 = Abs(r1.Value - r2.Value) < 1.04166666666667E-02
End Function

Signature

Gary''s Student - gsnu200789

> Can some help me with a time funtion or idea. I have a workbook that I am
> running a comparison between Sheet1 and Sheet2. I am comparing sheet1 columnP
[quoted text clipped - 19 lines]
> Next looper2
> Next looper

Reply to this Message

Mike - 31 May 2008 16:33 GMT

Im getting an object required
For looper2 = 2 To lastToCheckRow2
Set columnPcellPointer2 = xlWs2.Cells(looper2, 16)
If columnPcellPointer = columnPcellPointer2 Then
If within15(columnPcellPointer.Offset(0, -9).Value,
columnPcellPointer2.Offset(0, -9).Value) Then
xlWs.Range("A" &
columnPcellPointer.Row).EntireRow.Interior.ColorIndex = 6
End If
End If
Next looper2
Next looper

> Here is a function that will return true if two times are within 15 minutes
> of each other:
[quoted text clipped - 26 lines]
> > Next looper2
> > Next looper

Reply to this Message

Gary''s Student - 31 May 2008 16:39 GMT

Exactly... within15 needs ranges, not values as the arguments:

If within15(columnPcellPointer.Offset(0, -9), columnPcellPointer2.Offset(0,
-9)) Then

Signature

Gary''s Student - gsnu200789

> Im getting an object required
> For looper2 = 2 To lastToCheckRow2
[quoted text clipped - 39 lines]
> > > Next looper2
> > > Next looper

Reply to this Message

Mike - 31 May 2008 16:52 GMT

thanks gary has been a long day

> Exactly... within15 needs ranges, not values as the arguments:
>
[quoted text clipped - 44 lines]
> > > > Next looper2
> > > > Next looper

Reply to this Message

Gary''s Student - 31 May 2008 16:56 GMT

Glad to help.......Enjoy the Weekend!

Signature

Gary''s Student - gsnu200789

> thanks gary has been a long day
>
[quoted text clipped - 46 lines]
> > > > > Next looper2
> > > > > Next looper

Reply to this Message