blowing my mind

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
I am having a major headache importing a csv file into my workbook.

There is an issue with the way dates get changed when a worksheet is copied into my workbook.

the date is being written as text, if i double click on the cell the date transforms back to being formated as a date.

i recorded this to try to emulate in vba.

When recorded i get
Code:
 ActiveCell.FormulaR1C1 = "3/14/2009 17:39"

so as i want to loop this through a whole column i tried and use column a to check the number of lines of data i tried

Code:
Sub fixit()
Application.ScreenUpdating = False
Dim LR As Long
Dim cl As Range
Dim formu As String
With Sheets("Master!")
LR = .Range("A" & Rows.count).End(xlUp).Row
    For Each cl In .Range(.Cells(2, 1), .Cells(LR, 1))
    formu = cl.Offset(0, 3)
    cl.Offset(0, 3).formular1c1 = formu
    Next cl
End With

but this doesn't work - i have even tried copying the cell to another sheet and refrencing it in the vba looping for 2000 rows :

Code:
Sub fixit()
Application.ScreenUpdating = False
Dim LR As Long
Dim cl As Range
Dim formu As String
Dim counting As String
counting = 2
For LR = 2 To 2000
Sheets("master!").Select
Range("d" & counting).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A24").Select
ActiveSheet.Paste
Sheets("master!").Select
Range("d" & counting).Select
ActiveCell.FormulaR1C1 = Sheet1.Range("a24").Text
counting = counting + 1
Next LR
 
End Sub

Can anyone help?
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Select you column with "Dates" and choose Data>Text to Columns. On the 3rd panel of the wizard, select "Date" and a format. Click Finish. You're done
lenze
 
Upvote 0

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
lenze this works fine when i do it myself, i recorded the macro writing to column bb -i got the date value and the macro i get is
Code:
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("BB1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

when the code is run the cells are still showing as text?
 
Upvote 0

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
both the sopurce in column d and the cells in column bb - i beleive it is because the vba is redaing 14/03/2009 23:00 as mm/dd/yyyy hh:mm and as there is no 14th month will only read it as text
 
Upvote 0

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
On the 3rd panel of the wizard, did you select "DMY" from the dropdown?
lenze
 
Upvote 0

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
yes - when i follow the process myself it works fine, it is when i use the macro that the format doesn't change which is strange because the macro is the recording of me following
"Select you column with "Dates" and choose Data>Text to Columns. On the 3rd panel of the wizard, select "Date" and a format. Click Finish" which worked
 
Upvote 0

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
It appears the macro will work if the destination cellc(Range) is the same as the selected range, but not if a different column. Haven't a clue as to why, :confused:

lenze
 
Upvote 0

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
I ended up splitting the string into 4 columns dd mm yyyy and hh:mm then re-assembling the datein the next column
Code:
Sub timeent()

    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("AV1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        "/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Columns("AU:AU").Select
End Sub
Sub entdate()
Application.ScreenUpdating = False
Dim LR As Long
Dim cl As Range

With Sheets("master!")
    LR = .Range("A" & Rows.count).End(xlUp).Row
    For Each cl In .Range(.Cells(2, 1), .Cells(LR, 1))
     cl.Offset(0, 51) = cl.Offset(0, 48).Value & "/" & cl.Offset(0, 47).Value & "/" & cl.Offset(0, 49).Value
 cl.Offset(0, 51) = Format(cl.Offset(0, 51), "dd/mm/yyyy")
    Next cl
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,191,166
Messages
5,985,051
Members
439,935
Latest member
Monty238

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top