Macro: Altering cells in a range

[Mahnian]

I need to alter approximately 10,000 cells on a single sheet.
The range I need to replace is G2 through the end of the column, which
varies depending on call flow.

The cell is imported in one of the following two patterns:
9/27/2008 2:09:18 AM
10/27/2008 12:09:18 AM

I need the output of the cell to only be the date: 9/27/2008 or 10/27/2008

If anyone can shed any light on this for me, I would greatly appreciate it.

--Ian

 

[Gary Keramidas]

here's one way:

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim i As Long

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False

With ws
For i = 2 To lastrow
.Range("G" & i).Value = Split(Range("A1"))(0)
Next
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Does it have to be a macro? If not, select the entire column, click
Data/TextToColumns on the menu bar, select Delimited on the first panel and
click Next, check the Space checkbox and click Next, click the first column
in chart and pick Date-MDY in the option button group, click the other two
columns (one at a time) and select Do Not Import (Skip) from the option
button group, then click the Finish button.

 

[Mahnian]

Unfortunately, this did not work. I am not sure what it did but nothing
changed on the sheet.

 

[Gary Keramidas]

did you change the sheet name to match yours?

is the data in column G a date or a string? you didn't give a lot of
information to work with.

 

[Rick Rothstein]

By the way, if your entries are already real dates and if there are no
calculations being performed on that column's data where the time would
affect the calculation, then you can simply select the column and format it
as a Date using the first item in the list... the time will still be there,
but you won't see it.

Also, if you need to do this inside of a macro, this code line should do the
same thing in code as the procedure I originally posted...

Columns(1).TextToColumns ConsecutiveDelimiter:=True, Space:=True, _
DataType:=xlDelimited, FieldInfo:= _
Array(Array(1, 3), Array(2, 9), Array(3, 9))

 

[Gary Keramidas]

that's what i was trying to get at in my follow-up.

 

[Gary Keramidas]

rick:
have a question:

noticed something when i've recorded a macro using texttocolumns. if i just
selected fixed width , click finish then save it, and i then record another,
but go through the wizard and select text under column data format, the
code looks the same.

am i missing something?