Thursday, February 14, 2013

Excel strikes back - some VBA

From time to time a step back to basics is needed:
Sub CreateHeadersAndFirstRowFormulaes()
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "ProperDate"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=DATE(VALUE(LEFT($C2;4)); VALUE(MID($C2;5;2)); VALUE(RIGHT($C2; 2)))"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "ProperTime"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "=TIME(VALUE(LEFT($D2;2)); VALUE(RIGHT($D2;2));0)"
End Sub
Sub PropagateFormulaes()
    Dim lastrow As Long
    lastrow = Range("C" & Rows.Count).End(xlUp).Row
    Range("AA2:AB2").Select
    Selection.AutoFill Destination:=Range("AA2:AB" & lastrow)
End Sub