我有一些单元格包含以下公式(还有很多，但我只是举一个例子，它们都遵循相同的模式，在某些单元格内使用+或-等基本运算(
=+$O11+$N11+$M11
我需要将每一列向左移动一列，以便得到类似的结果
=+$N11+$M11+$L11
问题是，我已经编写了检测单元格是否有公式或只有值的代码
for each cell in Selection // im using a selection for testing purposes only
if cell.hasFormula() = true then
end if
next cell
但我仍在想如何将所有列引用向左移动一个，我写的唯一一个尝试这样做的代码不起的作用
auxiliary = "=offset(" + Replace(cell.formula, "=","") + ",0,1)"
cell.formula = auxiliary
更新1
有些公式只使用1个单元格来检查是否已设置，最多可使用8个引用单元格。数字或参考号围绕前面提到的2个数字移动
更新2
我发现以下名为"先例"的属性正在返回引用的范围，至少如果我将其应用于公式化引用，会这样做，即在第一个示例中，先例将返回$O$11:$M$11
更新3
除上述公式外，还有两种类型的公式，第一种是带和的公式，即
=Sum($R20:$AC20)
与国际单项体育联合会，即
=IF($BG20=0,1," ")
此公式中的所有单元格引用都必须向左移动1。
只要覆盖同一单元格，就可以尝试：
Option Explicit
Sub shiftLeft()
Dim f As String
Dim origCol As Long, newCol As Long
Dim r As Range, c As Range
Dim re As Object, mc As Object, m As Object
Dim I As Long, startNum As Long, numChars As Long
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = "C[?(-?d+)"
End With
Set r = Range(Cells(1, 9), Cells(Rows.Count, 9).End(xlUp))
For Each c In r
If c.HasFormula = True Then
f = c.FormulaR1C1
'Debug.Print f, c.Formula
If re.test(f) = True Then
Set mc = re.Execute(f)
For I = mc.Count To 1 Step -1
Set m = mc(I - 1)
startNum = m.firstindex + 1 + Len(m) - Len(m.submatches(0))
numChars = Len(m.submatches(0))
newCol = m.submatches(0) - 1
f = WorksheetFunction.Replace(f, startNum, numChars, newCol)
Next I
End If
End If
c.FormulaR1C1 = f
'Debug.Print f, c.Formula & vbLf
Next c
End Sub
我使用正则表达式来查找列名称，其形式为Cnn、C[nn]或C[-nn]然后我们可以从nn中减去一得到新的列号使用位置和长度来决定放置替换件的位置。
如果生成的公式引用列A左侧的列，则此宏将以运行时错误1004终止。您可能应该添加一个例程，这取决于您在该实例中想要做什么。
编辑：我没有测试以确保Cnn是有效的单元地址，而不是NAME。大多数情况下，除非你有一些非常不寻常的名字(例如Cnnnnnnnnn(，否则这无关紧要，因为与单元格地址冲突的名字会被拒绝，但如果你的C后面跟着一个大数字，它可能会被接受。如果可能存在问题，可以添加该测试
您可以使用助手临时工作表将数据/公式复制/粘贴到，删除第一列(并让公式向左移动一列(，然后粘贴回：
Dim tmpSht As Worksheet
Dim rng As Range
Set rng = Selection
Set tmpSht = Worksheets.Add
With rng
.Copy Destination:=tmpSht.Range(rng.Address).Offset(, -1)
End With
With tmpSht
.Columns(1).Delete
.Range(rng.Address).Offset(, -2).Copy Destination:=rng
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
或者你可以用公式对每个细胞进行操作：
Sub main()
Dim tmpSht As Worksheet
Dim cell As Range, rng As Range
Set rng = Selection
Set tmpSht = Worksheets.Add ' add a "helper" temporary worksheet
For Each cell In rng.SpecialCells(xlCellTypeFormulas) ' loop through selection cells containing a formula
ShiftFormulaOneColumnToTheLeft cell, tmpSht
Next
'delete "helper" worksheet
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End Sub
Sub ShiftFormulaOneColumnToTheLeft(rng As Range, hlpSht As Worksheet)
rng.Copy Destination:=hlpSht.Range("B1") ' copy passed range and paste it to passed "helper" worksheet range "B1"
With hlpSht ' reference passed "helper" worksheet
.Columns(1).Delete ' delete referenced worksheet first column and have all its formulas shift one column to the left
.Range("A1").Copy Destination:=rng ' copy "helper" worksheet "A1" cell (where previous "B1" has ended to) content to passed range
End With
End Sub
这将按预期移动/移动公式的引用：
strFormula$ = "=+$O11+$N11+$M11"
strFormulaMoved$ = Application.ConvertFormula(Application.ConvertFormula(Selection.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlR1C1, XlReferenceType.xlRelative, Selection), XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1, XlReferenceType.xlRelRowAbsColumn, Selection.Offset(0, -1))