|
Find and Replace Dates in VBA
If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it
works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Give this a try:
Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Can't get this solution to work, keep getting type mismatch errors.
"Don Guillett" wrote: From a post by Bob Phillips modified by Bernard V Liengme Public Sub ProcessData2() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i, TEST_COLUMN).Value = 0 Then .Cells(i, TEST_COLUMN).ClearContents End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Unfortunately, nothing happened when I ran this
"Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Sorry if this is a duplicate post, internet connection having a funny five
minutes. Nothing seemed to happen when I ran this solution. "Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
This requires you to select a group of cells prior to running the macro.
-- Gary''s Student - gsnu200753 "Ladymuck" wrote: Sorry if this is a duplicate post, internet connection having a funny five minutes. Nothing seemed to happen when I ran this solution. "Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
I tested.
What are you trying to do and exactly what did you do with the offering? -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... Can't get this solution to work, keep getting type mismatch errors. "Don Guillett" wrote: From a post by Bob Phillips modified by Bernard V Liengme Public Sub ProcessData2() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i, TEST_COLUMN).Value = 0 Then .Cells(i, TEST_COLUMN).ClearContents End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
The problem is that you are not stating dates clearly enough for Excel. I
recommend using the date function instead of entering dates like 01/12/2007. You would have something like: A B C D ------------------------------------------------------------------------ 1 'Date Year Month Day 2 '=Date(B2, C2, D2) 2007 1 12 This way VB never makes mistakes with dates. I must admit this solution requires you to change your workbook is written and that you might lack the time, but consider using it on new workbooks. -- Carlos Mallen "Ladymuck" wrote: Can't get this solution to work, keep getting type mismatch errors. "Don Guillett" wrote: From a post by Bob Phillips modified by Bernard V Liengme Public Sub ProcessData2() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i, TEST_COLUMN).Value = 0 Then .Cells(i, TEST_COLUMN).ClearContents End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
I copied and pasted it, replaced "A" with "00/01/1900" and kept getting type
mismatch errors on running it. Should I have done something else? "Don Guillett" wrote: I tested. What are you trying to do and exactly what did you do with the offering? -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... Can't get this solution to work, keep getting type mismatch errors. "Don Guillett" wrote: From a post by Bob Phillips modified by Bernard V Liengme Public Sub ProcessData2() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i, TEST_COLUMN).Value = 0 Then .Cells(i, TEST_COLUMN).ClearContents End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Didn't realise, will try again although I have to say I don't understand
quite what it's doing. Will this replace all instances of 00/01/1900 with an empty cell? There are no empty cells - they are all filled with dates. "Gary''s Student" wrote: This requires you to select a group of cells prior to running the macro. -- Gary''s Student - gsnu200753 "Ladymuck" wrote: Sorry if this is a duplicate post, internet connection having a funny five minutes. Nothing seemed to happen when I ran this solution. "Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
I'm just dealing with imported data. It is exported as csv with blank cells
where there are no dates against a particular item. I do a vlookup to pull the information I want and that gives me the result of 00/01/1900 where there is no date in the source field. The vlookup is one of many that is part of a nested IF that is currently four deep so I could investigate adding something else in to try and remove the zero dates. I was just hoping for a quick solution that would replicate find/replace as I will have to amend formulae across 24 columns that all look at varying criteria. "Carlos" wrote: The problem is that you are not stating dates clearly enough for Excel. I recommend using the date function instead of entering dates like 01/12/2007. You would have something like: A B C D ------------------------------------------------------------------------ 1 'Date Year Month Day 2 '=Date(B2, C2, D2) 2007 1 12 This way VB never makes mistakes with dates. I must admit this solution requires you to change your workbook is written and that you might lack the time, but consider using it on new workbooks. -- Carlos Mallen "Ladymuck" wrote: Can't get this solution to work, keep getting type mismatch errors. "Don Guillett" wrote: From a post by Bob Phillips modified by Bernard V Liengme Public Sub ProcessData2() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow If IsDate(.Cells(i, TEST_COLUMN).Value) And .Cells(i, TEST_COLUMN).Value = 0 Then .Cells(i, TEST_COLUMN).ClearContents End If Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Ladymuck" wrote in message ... If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Any chance you your dates are in specific columns?
If yes, then maybe you can change the number format of the columns to General, then change 0 to "". Then change the number format back to the date format you like. With ActiveSheet.Range("a:a,c:c,f:h") .NumberFormat = "General" .Cells.Replace what:="0", replacement:="", lookat:=xlWhole .NumberFormat = "mm/dd/yyyy" End With Ladymuck wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. -- Dave Peterson |
Find and Replace Dates in VBA
I tried it again using ActiveSheet.UsedRange.Select and Columns("J:AG").Select.
Nothing changed. "Gary''s Student" wrote: This requires you to select a group of cells prior to running the macro. -- Gary''s Student - gsnu200753 "Ladymuck" wrote: Sorry if this is a duplicate post, internet connection having a funny five minutes. Nothing seemed to happen when I ran this solution. "Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
Find and Replace Dates in VBA
Worked perfectly, thank you!
"Dave Peterson" wrote: Any chance you your dates are in specific columns? If yes, then maybe you can change the number format of the columns to General, then change 0 to "". Then change the number format back to the date format you like. With ActiveSheet.Range("a:a,c:c,f:h") .NumberFormat = "General" .Cells.Replace what:="0", replacement:="", lookat:=xlWhole .NumberFormat = "mm/dd/yyyy" End With Ladymuck wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. -- Dave Peterson |
Find and Replace Dates in VBA
If you select a block of cells and run the macro, any cell containing
00/01/1900 will be cleared. -- Gary''s Student - gsnu200753 "Ladymuck" wrote: Didn't realise, will try again although I have to say I don't understand quite what it's doing. Will this replace all instances of 00/01/1900 with an empty cell? There are no empty cells - they are all filled with dates. "Gary''s Student" wrote: This requires you to select a group of cells prior to running the macro. -- Gary''s Student - gsnu200753 "Ladymuck" wrote: Sorry if this is a duplicate post, internet connection having a funny five minutes. Nothing seemed to happen when I ran this solution. "Gary''s Student" wrote: Give this a try: Sub ladym() Set rr = Nothing For Each r In Selection If r.Text = "00/01/1900" Then If rr Is Nothing Then Set rr = r Else Set rr = Union(rr, r) End If End If Next If rr Is Nothing Then Else rr.Clear End If End Sub -- Gary''s Student - gsnu200753 "Ladymuck" wrote: If I do CRTL+ H and type in 00/01/1900 and set it to replace with nothing, it works fine. However, when I insert the following code into my VBA project, nothing happens. I took it straight from the good old Macro recorder! Cells.Replace What:="00/01/1900", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Removing the quotes automatically changed it to this, with the same result: Cells.Replace What:=0 / 1 / 1900, Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False What have I done wrong, or what do I need to? I know this is basically zero in date format but I don't want to replace everything that is 0, just those looking like a date Many thanks for your help. |
| All times are GMT +1. The time now is 05:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com