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

 

[MARK858]

Does the code below do what you want? Test on a copy of your data.

Sub Mark858a()
    With Cells(3, Columns.Count).End(xlToLeft).Resize(245, 1)
        .Replace What:="A", Replacement:=.Cells(1, 1).Value, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
    End With
End Sub

 

[MARK858]

Or possibly with your last code the below might be what you are after

Sub efliu()
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

and importantly are the "A" to replace actually capitals or are they lower case or a mixture?

 

[normanbox]

Thanks so much, we've made a lot of progress. The A is capitalized. Your last code sort of worked and so did my revised code I pasted earlier; however, the wrong column changes. I discovered that column A is being updated instead of the current column. I think it has to do with "xlToLeft", but when I replace it with "xlToRight" column As "A" text is replaced with nothing. It's the current column that should be updated with the new text heading. Lastly, instead of copying the text from the furthest right, it needs to be in the same column that I have data in. For example, starting in cell C6 to C245, I have data, such as text "BC", but I don't have data in cells D6-D245; however, I do have a heading D3, but also E3, F3, G3, etc. The data in column C needs to be replaced from text "A" (I copy the data from column B to each new column) to the column heading for C and eventually D, E, F, etc. I have a macro that copies the data from column D to the new column, which works fine. It's the replacing the text "A" with the heading of the new column that I'm having difficulty with.

 

[MARK858]

In plain simple terms what range is your data below the header in?
In other words what is the 1st cell in column A (A6?) and what is the last possible column?

 

[normanbox]

The first cell that has text, which is also the header is A3. The header extends to column JHU.

Starting in A6 through A245, I have data that specifically relates to column header in column A, in this case it's "A". I also have data in column B and C, but nothing yet in column D, even though I have a header for it. I copy the data from column A (again starting in A6) to column D, starting in D6; however, now I need to replace the reference to the header in column A to reference the header in column D. I will repeat the action until eventually all columns have the data with the appropriate reference in their respective column.

 

[MARK858]

I would try again to get a straight answer but first try the code below and comment...

Sub Mark858b()
    Dim LstCo As Long, LstRw As Long
    LstRw = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, True).Row
    LstCo = Rows("6:" & LstRw).Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, True).Column

    With Range(Cells(6, LstCo), Cells(LstRw, LstCo))
        .Replace What:="A", Replacement:=Cells(3, LstCo).Value, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
    End With
End Sub

Assumes your data to change starts in Row 6 AND your headers are in row 3

 

[normanbox]

That's perfect! It does exactly what I was trying to accomplish. Thank you very much and thank you Michael for your efforts as well!