VBA Find and Replace help needed

[bbalch]

Good morning and thanks in advance for your help.

A little information on what I'm trying to accomplish first. I have a reports where the cost center name is located in the title in cell $A$3 as "00-100 G&A". Throughout the remaining rows in column A the cost center name is repeated as "(00-100 G&A)". I clean the report up by finding and replacing "(00-100 G&A)" with "" in column A. I have approximately 100 different reports so the contents of cell $A$3 varies depending on the cost center. I would like to automate this and incorporate into a macro that I use to format the report.

I'm using the code below to find and replace the contents of A3 with C2 (blank). However, this code also finds and replaces the contents of cell A3, removing the cost center's name in the title.

1. Is there a way to adjust the code below to change the find and replace range so that it runs through cell A4 - end of column A?
2. The contents in cell A3 do not include the () around the name where rows A4- the end do eg. 00-100 G&A vrs (00-100 G&A). Is there a way to wrap the contents with () to find an replace (A3)? I've tried: Findtext = Range("("&"A3"&")").Value but that didn't do the trick.



Sub FindReplaceString()


Dim Findtext As String
Dim Replacetext As String


Findtext = Range("A3").Value
Replacetext = Range("C2").Value
Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


End Sub

Again, thank you for your help.

Ben

 

[JackDanIce]

To start with, see how far this takes you:

Sub FindReplaceString

Dim strFind as String
Dim strReplace as String
Dim LastRow as Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

strFind = Range("A3").Value
strReplace = Range("C2").Value

Range("A4:A" & LastRow ).Replace What:=strFind, Replacement:=strReplace, Lookat:=xlPart

End Sub

 

[bbalch]

That did the trick. Thank you!

I used the code below to get rid of the brackets:

Selection.Replace What:="(", Replacement:=""
Selection.Replace What:=")", Replacement:=""