插入复制的单元格，然后使用替换功能引用回原始单元格

Question

我目前正在尝试编写一个宏，该宏本质上是一列，将其复制到右侧，然后更改某些引用以引用回上一列。

我正在使用替换方法，但是因为我正在寻找基于列字母和"2"的特定字符串，所以我使用了一个对象将列字母和"2"组合在一起。更好的解释方法是假设我复制了 B 列并插入了它并在 C 列中复制了它。

我现在想在我的公式中找到"C2 和"C3"并将它们更改为"B2"和"B3"。

我认为这可以通过使用 replace 方法查找上述字符串并将它们偏移 -1 来完成。事实证明，这相当困难。有什么想法吗？

'duplicates column over 1'
ActiveCell.EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).EntireColumn.Select
Selection.Insert Shift:=xlToRight
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
Application.CutCopyMode = False
'Declarations and Instantiations'
Dim rngo As Range, cell As Range, ranger As Range
Dim lookfor As String
Dim UsedRng As Range, LastRow As Long
Set rngo = Selection.EntireColumn
Set UsedRng = ActiveSheet.UsedRange
LastRow = UsedRng(UsedRng.Cells.Count).Row
rngo.Select
Do Until ActiveCell.Row = LastRow + 1
For Each cell In rngo
Col = SPLIT(ActiveCell(1).Address(1, 0), "$")(0) 'returns just the cell letter'
lookfor = (Col & "2") 'combines the column letter with the number(I BELIEVE THIS IS THE SOURCE OF THE ISSUE BUT IM NOT SURE IN WHAT WAY'
'starts to search the new column for "lookfor" which is just the designated string'
rngo.Replace _What:=lookfor, Replacement:="'offset lookfor by 1 column'",_SearchOrder:=xlByRows, MatchCase:=True
Next cell
ActiveCell.Offset(1, 0).Select
Loop
MsgBox ColumnName(Selection)
MsgBox lookfor
End Sub

Answer 1

已解决：更改" rngo。Replace"改为"ActiveCell.Replace"