I'm trying to replace a formula in a few rows beneath a specific cell, which is in a different position every time the main macro is run.
The position of the ActiveCell as well as the range in which the formula needs to be replaced varies every time the macro is run.
I can successfully locate the ActiveCell dynamically and clear the contents of part of the row in which the ActiveCell is located, but I cannot successfully replace the formula in the data range beneath the ActiveCell.
Herewith my code that I have tried:
The position of the ActiveCell as well as the range in which the formula needs to be replaced varies every time the macro is run.
I can successfully locate the ActiveCell dynamically and clear the contents of part of the row in which the ActiveCell is located, but I cannot successfully replace the formula in the data range beneath the ActiveCell.
Herewith my code that I have tried:
Code:
'Clear contents of row with 'MCA Group' heading from Col B to last column with data
Cells.Find(What:="MCA Group", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(, 1).Resize(, 21).ClearContents 'Note: Range DIFFERS across all countries
Range(ActiveCell, Cells(Rows.Count, ActiveCell.End(xlToRight).Column).End(xlUp)).Select
For Each cell In Selection
cell.Formula = Replace(cell.Formula, "Detail!$C:$C", "Detail!$B:$B")
Next cell
Last edited: