Search and replace

[Guest]

I Have data in the following format:

A
--------
Wed Oct 17
data
data
data
data
..
..
..
Tue Oct 16
data
data
..
..
..
..

Basically I want the columns to look as follows:

A B
---------------
data Oct 17
data Oct 17
data Oct 17
data Oct 17
..
..
data Oct 16
data Oct 16
data Oct 16
..
..


It seems as if Excel does not recognize dates in that format. However if I
trim the left three characters it by default takes on the current year for
the specified date, which is fine with me.

My problem then is doing this in a macro, then adding this newly modified
date to column B, all the while removing the row which had the date in it in
the first place.

To summarize....
I need the date converted to something that excel recognizes. These dates
will always be in Column A, so I'd like to just search on that, I believe
it's A:A. No selection needed.

The really tricky part is that Mon and Tues, etc are in the same column, and
the corresponding data needs to have the correct dates, not just a blanket
date put in all of column B


Then I need to remove that initial row that had the date in it.


I know this is complex, but any help would be appreciated.

Thanks,

Matt

 

[Don Guillett]

What about the ....... s

 

[Guest]

Exactly......, can that be handled in a for-loop that searches for any of the
7 abbreviated days of the week?i.e. do what I had mentioned for each piece of
data before until you reach another cell with Mon, Tue, Wed, Thu, Fri, Sat,
or Sun.

And the .....s vary, but the days of the week won't.

Exactly why I'm posting this, it's a bit out of my realm. I've got the
concepts down, but I don't know the syntax well enough to really solve the
problem.

Thanks,

Matt

 

[Don Guillett]

I guess I should have asked the question better. Do you ACTUALLY have dots
a
..
..
..
b
or is it there just to show a continuation like
a
a
a
b
b
b
c
c
c
c

 

[Guest]

haha, sorry, it is to signify a continuation, there are not actually dots
there.

I found a way to trim the day of the week off of the beginning of the cells
that have the days in them.

Doing that by default makes them the correct date.

I guess what I need now is to figure out how to get the date next to the data.

Thanks again,

Matt

 

[Guest]

Ok, below is what I have got so far, I'm sure it can be cleaned up, but it's
doing what needs to be done thus far. I can get the date where I want it for
only 1 cell, I want to be able to do it for all ......s(all other data)


Sub fix_data()

' ######################
' This part removes all blank rows
' ######################
Dim Row As Long

Application.ScreenUpdating = False
With ActiveSheet
For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row
Step -1
If Application.CountA(.Rows(Row)) = 0 Then .Rows(Row).Delete
Next Row
End With
Application.ScreenUpdating = True

'#####################################################
' This part goes and changes all of the dates from the format Mon Oct 18 to
just Oct 18
'#####################################################

Range("A:A").Select
Cells.Replace What:="Mon ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Tue ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Wed ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Thu ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Fri ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Sat ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

Range("A:A").Select
Cells.Replace What:="Sun ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

'#########################################
' This part actually formats the date from Oct 18 to 10/18/2007
'#########################################

For Each c In Range("A:A")
If IsDate(c) Then c.NumberFormat = "m/d/yyyy;@"
If IsDate(c) Then c.Offset(1, 1).Value = c.Value
Next

End Sub