Line 50 in the code changes the format of column B from a date format to a number format so it can be compared to the table in Sheet "Times." Line 70 rounds the number value to three decimal places. You do understand that Excel stores Dates and Times as numbers and formats them only to look like familar dates and times. Dates are the integer portion of the number and times are the decimal portion of the number.
Here is a link to explain the whole concept: http://www.cpearson.com/excel/datetime.htm
I hope this helps you to understand the code better.
Code:
Option Explicit
Sub GetHour()
Dim w1 As Worksheet
10 Set w1 = Sheets("2012")
Dim w2 As Worksheet
20 Set w2 = Sheets("Times")
Dim lr As Long
30 lr = w1.Range("B" & Rows.Count).End(xlUp).Row
Dim i As Integer
40 Application.ScreenUpdating = False
50 w1.Range("B1:B" & lr).NumberFormat = "0.000"
60 For i = 1 To lr
70 w1.Range("B" & i).Value = Round(w1.Range("B" & i), 3)
80 Next i
90 For i = lr To 1 Step -1
Dim Res As Variant
100 On Error Resume Next
110 Err.Clear
120 Res = Application.WorksheetFunction.VLookup(w1.Range("B" & i), w2.Range("A2:A23"), 1, False)
130 If Err.Number <> 0 Then
140 w1.Range("B" & i).EntireRow.Delete
150 End If
160 Next i
170 Application.ScreenUpdating = True
End Sub
Reply With Quote
Bookmarks