Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." wrote in message ... 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" wrote: What about the ....... s -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." <Matt wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." wrote in message ... 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" wrote: What about the ....... s -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." <Matt wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and replace
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 "Matt P." wrote: 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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." wrote in message ... 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" wrote: What about the ....... s -- Don Guillett Microsoft MVP Excel SalesAid Software "Matt P." <Matt wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and Replace, or Add New Row | Excel Discussion (Misc queries) | |||
Search and Replace | Excel Discussion (Misc queries) | |||
Help with Search and Replace | Excel Programming | |||
search & replace | Excel Worksheet Functions | |||
Search and replace | Excel Programming |