filter specific month in dataset and replace each day of that filtered month with its first day (excel vba)

Question

this is a question on how to create a macro in VBA that would perform the following:

I would like to basically replace a month broken out by day (i.e 10/01/2015, 10/02/2015, etc...) with only the first day of the month (10/01/2015) in a specific column.

So the macro would "find" the column based on the column header (contract date) then it would replace all days for October 2015 (has to be year specific) with the first day of that month.

So I can get the macro to replace a certain pre-determined value:

Sub ReplaceTitleMs()
Columns("V").Replace    What:="10/02/2015", _
                        Replacement:="10/01/2015", _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False, _
                        SearchFormat:=False, _
                        ReplaceFormat:=False

But I can't figure out how to filter down to a specific month and replace multiple values (without listing every single possibilities in the macro).

Please help! :)

Tim Williams Tim Williams · Accepted Answer · 2015-10-21T05:34:23

Columns("V").Replace What:="10/*/2015", Replacement:="10/1/2015", _
                    LookAt:= xlPart, SearchOrder:=xlByRows, _
                    MatchCase:=False

filter specific month in dataset and replace each day of that filtered month with its first day (excel vba)

1 Answers