VBA 公式中的动态引用

debugcn 发表于 Dev

里斯·爱德华兹

我仅在满足特定条件时才尝试索引/匹配数据。

我可以用两个数组来做到这一点，但我希望这里有一个简单的答案。

我的代码如下：

Sub Nozeroleftbehind(lengthRow As Integer)
For i = 2 To lengthRow
    If Cells(1, i) = 0 Then Cells(1, i) = "TBD"
Next i

For i = 2 To lengthRow
    If Cells(1, i) = "#N/A" Then
        Cells(2, i) = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H113,Forecast!A:A,0))"
End if
Next i

    End Sub

然后将该子程序传递回主程序。

我想要动态的是'H113'单元格。我似乎无法获得偏移以正常工作，因为它已经在一个公式中。

编辑：抱歉，H113 向下移动。下一个单元格将是 H114。

问候

变异

请试试这个代码。

Sub NoZeroLeftBehind(lengthRow As Integer)
    ' 18 Oct 2017

    Dim lengthRow As Long
    Dim Tmp As Variant
    Dim C As Long

    lengthRow = 4
    For C = 2 To lengthRow
        ' bear in mind that the Cell is a Range
        ' and you want to refer to its Value & Formula property
        With Cells(1, C)
            Tmp = .Value
            ' using the Val() function will interpret a blank cell as zero value
            If Val(Tmp) = 0 Then
                .Value = "TBD"
            ElseIf IsError(Tmp) Then
                .Formula = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H" & _
                           (113 + C - 2) & ",Forecast!A:A,0))"
            End If
        End With
    Next C
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。