Hey, so I'm trying to use the AutoFill with Cells command an I am having trouble. Here is my code
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(DATEVALUE(RC[-1]))"
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range(Cells(2, 12), Cells(lastRow2, 12)), Type:=xlFillDefault
'time conversion
Columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)&"" ""&RIGHT(RC[-1],2)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N452"), Type:=xlFillDefault
here is what I want
"date conversion
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(DATEVALUE(RC[-1]))"
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range(Cells(2, 12), Cells(lastRow2, 12)), Type:=xlFillDefault
'time conversion
Columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)&"" ""&RIGHT(RC[-1],2)"
Range("N2").Select
Selection.AutoFill Destination:=Range(Cells(2, 13), Cells(lastRow2, 13)), Type:=xlFillDefault
I'm confused because it works during the Date conversion but not the time conversion. also last row is a variable found by the following function.
Dim lastRow2 As Long
Dim row As Long
Dim startRow As Long
Dim col As Long
startRow = 1
col = 1
lastRow2 = Findlastrow(col)
Function Findlastrow(col) As Long
Findlastrow = Cells(Rows.Count, col).End(xlUp).row
End Function
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(DATEVALUE(RC[-1]))"
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range(Cells(2, 12), Cells(lastRow2, 12)), Type:=xlFillDefault
'time conversion
Columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)&"" ""&RIGHT(RC[-1],2)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N452"), Type:=xlFillDefault
here is what I want
"date conversion
Range("L2").Select
ActiveCell.FormulaR1C1 = "=(DATEVALUE(RC[-1]))"
Selection.NumberFormat = "m/d/yyyy"
Selection.AutoFill Destination:=Range(Cells(2, 12), Cells(lastRow2, 12)), Type:=xlFillDefault
'time conversion
Columns("N:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("N2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)&"" ""&RIGHT(RC[-1],2)"
Range("N2").Select
Selection.AutoFill Destination:=Range(Cells(2, 13), Cells(lastRow2, 13)), Type:=xlFillDefault
I'm confused because it works during the Date conversion but not the time conversion. also last row is a variable found by the following function.
Dim lastRow2 As Long
Dim row As Long
Dim startRow As Long
Dim col As Long
startRow = 1
col = 1
lastRow2 = Findlastrow(col)
Function Findlastrow(col) As Long
Findlastrow = Cells(Rows.Count, col).End(xlUp).row
End Function