| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a Time function or ... Something
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.ColorIn dex = 6 End If End If Next looper2 Next looper |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a Time function or ... Something
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 -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a Time function or ... Something
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.ColorIn dex = 6 End If End If Next looper2 Next looper "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a Time function or ... Something
Exactly... within15 needs ranges, not values as the arguments:
If within15(columnPcellPointer.Offset(0, -9), columnPcellPointer2.Offset(0, -9)) Then -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a Time function or ... Something
thanks gary has been a long day
"Gary''s Student" wrote: Exactly... within15 needs ranges, not values as the arguments: If within15(columnPcellPointer.Offset(0, -9), columnPcellPointer2.Offset(0, -9)) Then -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a Time function or ... Something
Glad to help.......Enjoy the Weekend!
-- Gary''s Student - gsnu200789 "Mike" wrote: thanks gary has been a long day "Gary''s Student" wrote: Exactly... within15 needs ranges, not values as the arguments: If within15(columnPcellPointer.Offset(0, -9), columnPcellPointer2.Offset(0, -9)) Then -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200789 "Mike" wrote: 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.ColorIn dex = 6 End If End If Next looper2 Next looper |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there an XLS function to convert std time to Military time? | Excel Worksheet Functions | |||
| Macro / function text time to 24hr excel time passed midnight fortotaling hr's | Excel Worksheet Functions | |||
| verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
| Function Call Specified at Run Time rather than Compile Time? | Excel Programming | |||
| Function to convert Time String to Time | Excel Worksheet Functions | |||
Linear Mode
