Variable Replace

[normanbox]

I have a simple macro I'm having difficulty with. I'm trying to complete a copy and replace using text from a row. For example, in the code below, the replacement is "BC", which is fine if that's the heading, but if the heading changes to "BD" I need the replacement to be "BD", but I can only get the code to keep the same replacement text of "BC". What can I do so the paste in the replacement field what I copied, not "BC"?
Range("B6").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(-3, 0).Range("A1").Select
Selection.Copy
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A245").Select
Selection.Replace What:="A", Replacement:="BC", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

 

[Michael M]

What do you mean by

if the heading changes to "BD"

Is this the column reference or actual text in a heading row, and if so, which row is it in !!

 

[normanbox]

It's not a column reference, but actual text. It's currently in column G, while BC is in F. The macro is designed to find the last column of text, highlight and replace with BC, or BD, or whatever will come next. I have another macro that places my data in a new consecutive column, but I need to replace the letter "A" with the new text in that specific column.

 

[Michael M]

Again....which row is the heading text in ??

 

[normanbox]

Sorry, it's in row 3.

 

[Michael M]

Does this do as required....don't have Excel at the moment so is UNTESTED

Sub MM1()
Dim lc As Integer
lc = Cells(3, Columns.Count).End(xlToLeft).Column
Range("A1:A245").Replace What:="A", Replacement:=Cells(3, lc).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
End Sub

 

[normanbox]

Thank you, but unfortunately it doesn't seem to be working.

 

[Michael M]

Sadly, that response doesn't help

it doesn't seem to be working.

What is / isn't happening ??
Is there an error mesage ??

 

[MARK858]

Sadly, that response doesn't help

What is / isn't happening ??
Is there an error mesage ??

Hi Michael, I think you might need to query the range the replace is working on as the way the OP has the code written it isn't actually applying to Range("A1:A245").

ActiveCell.Range("A1:A245").Select

is acting in the OP's code as a resize i.e. ActiveCell.resize(245,1), I am not sure that is what the OP wants but it is what their code does.

 

[normanbox]

I updated the code to the following:

Dim lc As Integer
lc = Cells(3, Columns.Count).End(xlToLeft).Column
ActiveCell.Range("A1:A245").Select
Range("A1:A245").Replace What:="A", Replacement:=Cells(3, lc).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True
End Sub

When the code finishes running, the cells are selected, but the "A" text is not replaced with the column text heading, (i.e. "BB", "AAC", etc). As a reminder, the column heading is not the column. "BB" could as easily say "Dog", which is what I need to replace the "A" text heading.

Thanks again for the help.