Replace Text in a Text String

[HangMan]

I have a file the details the filepath to thousands of folders in the format:

C:\Client Jobs\Test\East\Folder1
C:\Client Jobs\Test\East\Folder2
C:\Client Jobs\Test\East\Folder3

C:\Client Jobs\Test\West\Folder1
C:\Client Jobs\Test\West\Folder2
C:\Client Jobs\Test\West\Folder3

etc... (see attached)

The number of folders can vary from one to n. I would like to be able to run a macro that replaces every instance 'Folder1' with different text, e.g. 'Accounts', 'Folder2' with say 'Reports' and so on, so it basically cycles through 'Folder1' to 'FolderN' replacing the text with what ever I define. I know I could do this with a simple Find/Replace, but this will be a repetitive requirement an a macro approach would dramatically speed things up.

Is this possible?

Many thanks

[berlan]

Try,

Columns("A").Replace "\Folder1", "\Accounts", xlPart, , True

changing the replacement text as needed in the script.

[berlan]

And a bit more specified:

Sub test()
    Dim i As Integer
    For i = 1 To 6
        ActiveSheet.UsedRange.Replace "\Folder" & i, Choose(i, "Accounts", "Reports", "NewFolder3", "NewFolder4", "NewFolder5", "NewFolder6"), xlPart, , True
    Next
End Sub

[HangMan]

Hi berlan,

I removed the \ before Folder and it worked like a charm, perfect...

Many thanks for your help

[berlan]

Glad it worked, you're welcome!