Aplication to import and Convert Data

[Guest]

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

 

[Guest]

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

 

[Guest]

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

 

[Guest]

I would guess that you didn't copy all the code since it does include an End
Sub statement in what I posted. Also, I tested it and the code was copied
directly from the module where it was working exactly as expected.

 

[Guest]

Tom,

I am getting another error (a debug error)

the following line is highlighted

" dt = CDate(m & "/" & d & "/" & y)"

Can I send you the file I have and maybe you can fixed it for us.

Please I really need your help

 

[Tom Ogilvy]

Chris, you can send it. Make sure you include an email I can send it back
to.
(e-mail address removed)