VBA公式中的动态参考公式
问题描述:
我试图仅在满足特定条件时才对数据进行索引/匹配。VBA公式中的动态参考公式
我可以做两个数组,但我希望这里有一个简单的答案。
我的代码如下:
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
+0
谢谢!不得不稍微改变它,但它完美无缺!非常感激。 –
答
知道你想要去的H113,H114:
Cells(2, i) = "=INDEX(Forecast!L:L,MATCH('AA - Inbound Orders Weekly Rep'!H" & CStr(111 + i) & ",Forecast!A:A,0))"
请澄清一下:如果循环过程是“动态的”,H113会是什么样子? I113,J113或H114,H115 ...? – Excelosaurus
H114。对不起,没有。 –