Excel Forum

Code: Select all

Sub CalculateTimeIN()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("J:J").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("Q:Q").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("R:R").Select
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""?_);_(@_)"
    Columns("I:I").Select
    For Each Kob In Selection
    Kob.Formula = Kob.Formula
    Next
    Columns("Q:Q").Select
For Each Kob In Selection
    Kob.Formula = Kob.Formula
    Next
    ActiveCell.FormulaR1C1 = "TIME8"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]>=(--""7:45 AM""),RC[-2]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>=(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J4224")
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
    Selection.AutoFill Destination:=Range("R2:R4224")
End Sub

Code: Select all

    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J4224")
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
    Selection.AutoFill Destination:=Range("R2:R4224")

Code: Select all

Sub CalculateTimeIN()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("J:J").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("Q:Q").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("R:R").Select
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""?_);_(@_)"
    Columns("I:I").Select
    Columns("I:I").Value = Columns("I:I").Value
'    For Each Kob In Selection
'    Kob.Formula = Kob.Formula
'    Next
    Columns("Q:Q").Select
    Columns("Q:Q").Value = Columns("Q:Q").Value
'    For Each Kob In Selection
'    Kob.Formula = Kob.Formula
'    Next
    ActiveCell.FormulaR1C1 = "TIME8"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]>=(--""7:45 AM""),RC[-2]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>=(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
'    Selection.AutoFill Destination:=Range("J2:J4249")
    Selection.Resize(Range("A" & Rows.Count).End(xlUp)).FillDown
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
'    Selection.AutoFill Destination:=Range("R2:R4249")
    Selection.Resize(Range("A" & Rows.Count).End(xlUp)).FillDown
End Sub