Greetings,

I need to perform a 'find and replace' on a worksheet to change all references to a particular month to the following month i.e. find “Month **” (**=could be any number 01 - 12) and replace with the contents of cell “AE2” (the cell reference where the current Month ** is generated).

I have written the very simple code below and assigned it to a button so that it can be used by multiple staff in the department, and the find and replace part is seemingly fine. However, when replacing with the new text, it doesn’t just replace Month ** with Month **, it also removes any other text that may have been present in the cell after Month ** is present

Sub Find_Replace()

Columns("C:AC").Select
Selection.Replace What:="Month **", Replacement:=Range("AE2"), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
End Sub
For example:

‘Financial Statements Month 05 2014/15’ becomes ‘Financial Statements Month 06’ (' 2014/15' is lost)

But more importantly, the filepath which drives the attachment on the subsequent mail merge changes from:

‘N:\folder 1\folder 2\Month 05\filename.xls’ to N:\folder 1\folder 2\Month 06’. ('\filename.xls' is lost)

The path is therefore incomplete and the attachment is not added to the email.



Is there a way of altering my code so that the replace will work mid-text string and keeping everything else as it originally was?

many thanks in advance.

Phil