所以，我试图构建代码，其中AE中的单元格可以通过手动输入覆盖，但根据给定条件引用不同的范围。我已经看了好几个小时了，但似乎还是看不出来。任何帮助都是感激不尽的。
我得到的错误是：公式对象的方法范围失败
突出显示这行代码

.范围（“AE”& i）.公式= BM公式& i

Public Sub BedoverrideND(Bathmat As Worksheet, DSMaster As Worksheet, DSMformula As String, BMformula As String)
Dim i As Long

For i = 7 To getcostcoderange
    Select Case i
    Case 21, 22, 23, 24, 25
'        Do nothing
    Case Else
        If Bathmat.Range("B" & i).Value = 0 Or Bathmat.Range("B" & i).Value = "" Or _
            Not WorksheetFunction.IsFormula(Bathmat.Range("AE" & i)) _
            And Not IsEmpty(Bathmat.Range("AE" & i)) Then
        Else

            With Bathmat

                If Not WorksheetFunction.IsFormula(.Range("E" & i)) And Not IsEmpty(.Range("E" & i)) Then
                    .Range("AE" & i).Formula = BMformula & i
                ElseIf DSMaster.Range("T" & i).Value + DSMaster.Range("U" & i).Value <> 0 Then
                    .Range("AE" & i).Formula = DSMformula & i
                Else
                    .Range("AE" & i).Formula = BMformula & i
                End If
            End With
        End If
    End Select
Next i
End Sub

这些是启动潜艇的密码

Public Sub BedOverrideSun()
    BedoverrideND shBathMatSun, shDSMasterSun, "='DS Master Sun'!V", "='Bath Mat Sun'!E"
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("E:E, AE:AE, BE:BE")) Is Nothing Then
 Call BedOverrideSun

我试了一下，找到了我想要的解决方案：

With Bathmat

        If Not WorksheetFunction.IsFormula(.Range("E" & i)) And Not IsEmpty(.Range("E" & i)) Or _
            DSMaster.Range("T" & i).Value + DSMaster.Range("U" & i).Value = 0 Then
                If .Range("AE" & i).Formula <> BMFormula & i Then
                    .Range("AE" & i).Formula = BMFormula & i
                End If
        ElseIf DSMaster.Range("T" & i).Value + DSMaster.Range("U" & i).Value <> 0 Then
            If .Range("AE" & i).Formula <> DSMaster & i Then
                .Range("AE" & i).Formula = DSMaster & i
            End If
        End If
    End With