VBA Find and Replace (Tidy Up)

B

bbcdancer

Is there a more efficient way to write this VBA code to Find and
Replace weird date values in columns G to I as specified in the VBA
code below.
Note: in some cells in columns G to I may be blank so I don't really
need a looping VBA code to check each cell, Find and Replace, until it
reach blank cell.

Tidying the code would be nice.

Many Thanks in advance.

Sub WrongDatesValues()
Range("G:I").Select
ActiveCell.Replace What:="29-02", Replacement:="28-02",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="29-02", Replacement:="28-02",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="30-02", Replacement:="28-02",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="30-02", Replacement:="28-02",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="31-02", Replacement:="28-02",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="31-02", Replacement:="28-02",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="31-04", Replacement:="30-04",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="31-04", Replacement:="30-04",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="31-06", Replacement:="30-06",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="31-06", Replacement:="30-06",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="31-09", Replacement:="30-09",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="31-09", Replacement:="30-09",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Replace What:="31-11", Replacement:="30-11",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Selection.Replace What:="31-11", Replacement:="30-11",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Macro to select columns and replace data 0
Find and Replace Help 3
Help with a loop please. 10
replace 29 february with 28 february with vba 4
Need help with a "If" "Else" VB code 4
Need help with correct syntax please 2
Printing Macro 4

Top