kirby1024
10-22-2008, 07:32 PM
Hello All,
I'm running an Excel 2003 file, where I put some data in from a csv file (which has >6000 rows currently). Specifically, the data I'm using is a date field within that data, but copying it from the csv file gives it to me as a text field, which is useless for all the formulas I've set up.
So! I figured a simple and easy way to do it would be to create a Macro in Worksheet_change for the data sheet, that would go through every entry in the date column, changing the text there to date format until it hit a blank cell. This worked fine (if a touch slow as it used a loop), so I left it as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A3").Select
Do While IsEmpty(ActiveCell.Value) = False
ActiveCell.Value = CDate(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Then I put the formulas in. Now, what was acceptably slow is basically taking forever, as every iteration of the loop invokes a formula recalculation!
So, I have two questions:
1) Is it possible to disable formula recalculation during a script? I've tried using
Application.EnableEvents = False to turn it off, but of course since I've put it in an Application event... Also, doesn't seem to affect formula recalculation anyway.
2) Is there a way that I can do what I want to do (changing a text-version of a date into a date value) without the loop? The formula recalculation wouldn't be such a big issue if it didn't happen every time. For comparison, the format that the CSV file spews at me is:
Oct 23, 2008 12:35:53 PM
I've tried just changing the cell variable normally, but for some reason it won't budge, hence this workaround.
Help?!
I'm running an Excel 2003 file, where I put some data in from a csv file (which has >6000 rows currently). Specifically, the data I'm using is a date field within that data, but copying it from the csv file gives it to me as a text field, which is useless for all the formulas I've set up.
So! I figured a simple and easy way to do it would be to create a Macro in Worksheet_change for the data sheet, that would go through every entry in the date column, changing the text there to date format until it hit a blank cell. This worked fine (if a touch slow as it used a loop), so I left it as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A3").Select
Do While IsEmpty(ActiveCell.Value) = False
ActiveCell.Value = CDate(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Then I put the formulas in. Now, what was acceptably slow is basically taking forever, as every iteration of the loop invokes a formula recalculation!
So, I have two questions:
1) Is it possible to disable formula recalculation during a script? I've tried using
Application.EnableEvents = False to turn it off, but of course since I've put it in an Application event... Also, doesn't seem to affect formula recalculation anyway.
2) Is there a way that I can do what I want to do (changing a text-version of a date into a date value) without the loop? The formula recalculation wouldn't be such a big issue if it didn't happen every time. For comparison, the format that the CSV file spews at me is:
Oct 23, 2008 12:35:53 PM
I've tried just changing the cell variable normally, but for some reason it won't budge, hence this workaround.
Help?!