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

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

--


Gary

"Mahnian" wrote in message
...
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

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.

--
Rick (MVP - Excel)


"Mahnian" wrote in message
...
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

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

"Gary Keramidas" wrote:

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

--


Gary

"Mahnian" wrote in message
...
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

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.

--


Gary

"Mahnian" wrote in message
...
Unfortunately, this did not work. I am not sure what it did but nothing
changed on the sheet.

"Gary Keramidas" wrote:

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

--


Gary

"Mahnian" wrote in message
...
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

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))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Mahnian" wrote in message
...
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

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

--


Gary

"Rick Rothstein" wrote in message
...
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))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Mahnian" wrote in message
...
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

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?

--


Gary

"Rick Rothstein" wrote in message
...
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))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Mahnian" wrote in message
...
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