VBA causing time values to go to AM/PM

[eddm87]

Using imported data, I need to activate text to column on a column to be able to use the data, sum etc. This is column B in the below shot.

This works fine when manually doing it however I am trying to create a VBA code which does various things, put data in table, delimits column A etc.

The code is...

Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("B1").Select
ActiveCell.FormulaR1C1 = "Time"
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$S"), , xlYes).Name = _
"Table1"
Columns("A:S").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium6"
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select

The below is what I end up with (column C is the one in question)

It doesn't matter if it is fixed width or delimited it always reverts to PM or AM. I have changed the format to [h]:mm:ss before VBA, after VBA, within the code etc, doesn't make a difference.
Any help would be great.

Thanks all.

 

[eddm87]

To anyone interested. Spent another couple of hours on it and found that column A was delimiting two other values, one a time, one a PM/AM, I just couldn't see it. I created an additional column, then deleted it.
Updated VBA below.

Columns("B:B").Select
Range("B:B").Value = Range("B:B").Value
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("C:C").Delete
Columns("C:C").Select
Selection.NumberFormat = "[h]:mm:ss"
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$S"), , xlYes).Name = _
"Table1"
Columns("A:S").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium6"
Range("Table1[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Time"