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
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
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 :
Can anyone help?
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: