Changing Macro Code from Absolute to Relative Reference

Joined
Mar 27, 2013
Messages
5
I used the macro recorder to replace certain text in the range A3:A5000 (in this case [M]) with the text in cell A2 (in this case "khols"). I want to be able to run the same thing for all columns in the spreadsheet, but replace B3:B5000 with text in B2, C3:C5000 with text in C3, etc.

The code I'm using is:

Sub MerchantReplaceTest1()
'
' MerchantReplaceTest1 Macro
'


'
ActiveCell.Range("A3:A5000").Select
Selection.Replace What:="[M]", Replacement:="khols", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


How do I edit this to be able to run for all columns in the sheet?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
You can use eg:

Replacement:=Range("B2").Value

Are you sure that you need ActiveCell in the first line? If it's not A1 you may not get the results you expect.
 
Upvote 0
That's just what came out of the macro recorder, so I'm really not sure whether I need it or not. Ideally, I'd like to be able to apply the macro to any column without having to alter the code each time. Is that possible?
 
Upvote 0
I used the macro recorder to replace certain text in the range A3:A5000 (in this case [M]) with the text in cell A2 (in this case "khols"). I want to be able to run the same thing for all columns in the spreadsheet, but replace B3:B5000 with text in B2, C3:C5000 with text in C3, etc.
The following assumes the only things in cells you want to do the replacements on is data (constants)... no formulas! If you meet that condition, then consider this...

The macro recorder can never return the following code (a one-liner!), but I believe it will do what you want and do it quite efficiently, just replace all the R's that I highlighted in red (four of them) with the column letter designation for the last column containing your data...
Rich (BB code):
Sub Relacements()
  Range("A3:R5000") = Evaluate("IF(LEN(A3:R5000),SUBSTITUTE(A3:R5000,""[M]"",A2:R2),"""")")
End Sub
 
Upvote 0
That's just what came out of the macro recorder, so I'm really not sure whether I need it or not. Ideally, I'd like to be able to apply the macro to any column without having to alter the code each time. Is that possible?
The code I gave you in my previous message processes all the columns at once (which is what I thought you were asking for in your original message); however, your latest message makes it sound like you want to pick a column an replace all the [M] text between Rows 3 and 5000 with whatever is in the cell on Row 2 of that column. If so, and still assuming those cells contain constant data only, then you can use the following code. Simply select any cell in the column you want to process and then run this macro...
Code:
Sub Relacements()
  Dim Addr As String
  Addr = Intersect(Rows("3:5000"), ActiveCell.EntireColumn).Address
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),SUBSTITUTE(" & Addr & _
                ",""[M]""," & Cells(2, ActiveCell.Column).Address & "),"""")")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top