NeelsBorstlap
New Member
- Joined
- Jul 23, 2010
- Messages
- 25
Hi
Working with Excel 2016
I'm receiving a "csv" file from a 3rd party program with dates in column "A" but it is text and I can not format it to a date in excel.
I'm looking for a VBA code that will take the text values in column "A" like 16/09/2015 for 16-Sep-2015 and like 03/11/2015 for 3-Nov-2015 and covert it to column "S" as a date like
16-Sep- 2015 and 3-Nov-2015. (In each file the number of rows with data is different.)
In column "R" the process must be repeated but all the "days" must be change to one day (say the 1st of that month) for all the text in column "A" and the format to look like this
Nov-15 for 01/11/2015 or 1-Nov-2015.
At the end it must replace the data in column "A" and insert a new column in column "B" and the "MONTH" data must go there
I did record a code for this but it involve copy and paste and it is very slow in running - very inefficient. See code below.
I want to add sample of the data but do not how to post that or attached it.
Thank for you help and time
Working with Excel 2016
I'm receiving a "csv" file from a 3rd party program with dates in column "A" but it is text and I can not format it to a date in excel.
I'm looking for a VBA code that will take the text values in column "A" like 16/09/2015 for 16-Sep-2015 and like 03/11/2015 for 3-Nov-2015 and covert it to column "S" as a date like
16-Sep- 2015 and 3-Nov-2015. (In each file the number of rows with data is different.)
In column "R" the process must be repeated but all the "days" must be change to one day (say the 1st of that month) for all the text in column "A" and the format to look like this
Nov-15 for 01/11/2015 or 1-Nov-2015.
At the end it must replace the data in column "A" and insert a new column in column "B" and the "MONTH" data must go there
I did record a code for this but it involve copy and paste and it is very slow in running - very inefficient. See code below.
I want to add sample of the data but do not how to post that or attached it.
Code:
Sub dateconvert ()
Columns("A:A").Select
Selection.Copy
Range("S1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("S1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Range("V1").Select
Selection.End(xlDown).Select
Range("U65536").Select
Selection.End(xlUp).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Range("V2").Select
ActiveCell.FormulaR1C1 = "=+RC[-1]&""/""&RC[-2]&""/""&RC[-3]&""n"""
Range("V2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("W2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="n", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("V2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("X2").Select
Application.CutCopyMode = False
Range("R2").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveCell.FormulaR1C1 = "1"
Range("R3").Select
Selection.End(xlDown).Select
Range("S65536").Select
Selection.End(xlUp).Offset(0, -1).Select
' Range("R11259").Select
ActiveCell.FormulaR1C1 = "1"
Selection.End(xlUp).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("S2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("V2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("X2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="n", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "[$-409]mmm-yy;@"
Range("W2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B2").Select
Selection.EntireColumn.Insert
Range("B1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("N:Z").Select
Selection.Clear
End SubThank for you help and time
