Macro or other to Search a date and replace with new date with a condition

Antispam246

New Member
Joined
Apr 4, 2015
Messages
9
I have two date columns B and C both formatted to date xx/xx/xx

I want to be able to (ideally) run a macro which searches for a specific date in column B and replaces it with a new one but only if column C is empty.

As the dates that will change further down the line will be manually updated as they are random it can't be automatic and I can manually edit the date requirements in said macro prior to the next run.

Any help appreciated
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,380
Try this:
Code:
Private Sub CommandButton1_Click()
'Modified  10/6/2019  5:05:41 AM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim i As Long
Dim Lookfor As String
Dim ReplaceWith As String
Lookfor = InputBox("Enter date to look for", "Today is  " & Date)
ReplaceWith = InputBox("Enter date To enter", "Today is  " & Date)

    For i = 1 To Lastrow
        If Cells(i, 2).Value = Lookfor And Cells(i, 3).Value = "" Then Cells(i, 2).Value = ReplaceWith
    Next
    
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,537
Office Version
2007
Platform
Windows
Try this

Change 2019,09,21 (yyyy,mm,dd) For the date to replace
Change 2019,10,15 (yyyy,mm,dd) For the new date

Code:
Sub ReplaceDate()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},IF(" & .Address & "=DATE([COLOR=#ff0000]2019,09,21[/COLOR]),IF(" & .Offset(, 1).Address & "="""",DATE([COLOR=#0000ff]2019,10,15[/COLOR])," & .Address & ")," & .Address & "))")
  End With
End Sub
 

Antispam246

New Member
Joined
Apr 4, 2015
Messages
9
Thank you both, I ran into issues with the first lot of code, an error stating macros weren't enabled. The second lot Sub ReplaceDate() worked perfectly.

Going to do abit more testing on the test sheet before converting the original to macro enabled but all seems good, thank you!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,537
Office Version
2007
Platform
Windows
Im glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,084,796
Messages
5,379,933
Members
401,633
Latest member
DinnerB0ne

Some videos you may like

This Week's Hot Topics

Top