CODEWITHSUNDEEP
vbaexcel
Joe.L
Joe.L

Reputation: 3

Replacing text within formulae on Excel

I have a spreadsheet with sheets that contain standard data tables and one sheet for calculations. I want to set it up so that a macro searches for the name of the data table sheet currently being used in calculations and replaces it with the name of another data table sheet. As the data tables have the same layout nothing else in the formulae would need to change, however I'm struggling! This is what I have so far:

Sub Replace_Text()

  Dim Findtext As String
  Dim Replacetext As String
  Findtext = "Front Sheet!B2"
  Replacetext = "Front Sheet!C2"
  Worksheets("Sheet 2").Range("K11:Z91").Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=False

As you can see I'm trying to have a cell reference for the text so that the only thing anyone would need to change is what's written in those cells (B2, C2) to get calculations for a new data table.

I believe the error is in referencing the range to search through? As I'm quite new to using Excel I don't really know what else to try.

Upvotes: 0

Views: 76

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34420

You just need to get the contents of B2 and C2 (actually you can leave out .value but it seems clearer to me with it in):-

Sub Replace_Text()
Dim Findtext As String
Dim Replacetext As String
Findtext = Worksheets("Front Sheet").Range("B2").Value
Replacetext = Worksheets("Front Sheet").Range("c2").Value
Worksheets("Sheet2").Range("K11:Z91").Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=False
End Sub

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

you'll be passing in a string of where to find the value, rather than the value, so findtext=worksheets("Front Sheet").range("b2").value

Upvotes: 1

Related Questions