I was wondering if you could help with the following. I have just started using VBA but I am having an issue with applying the following formula to the dataset.
我想知道你是否可以帮助以下。我刚刚开始使用VBA,但我遇到了将以下公式应用于数据集的问题。
Selection.Formula = "=IFNA(INDEX('beach'!B:B,MATCH(F19,'beach'!$G:$G,0)),0)"
F19 is a set cell, however, I need F19 to be sequential, as in that when the calculation is completed in cell M20
rather than M19
, the formula is updated to calculate using F20
.
F19是设定单元,但是,我需要F19是顺序的,因为当在单元格M20而不是M19中完成计算时,更新公式以使用F20计算。
Also, following on from that how do I edit the above formula to allow the macros to be applied to all cells in a Column?
此外,继续之后如何编辑上述公式以允许将宏应用于列中的所有单元格?
2 个解决方案
#1
#2
1
You can also use R1C1
style.
您也可以使用R1C1风格。
Say you want that formula to apply in range M19:M20
with your F19
reference updating to each row:
假设您希望该公式适用于范围M19:M20,并且您的F19参考更新到每一行:
Range("M19:M20").FormulaR1C1 = "=IFNA(INDEX(beach!C[-11],MATCH(RC[-7],beach!C7,0)),0)"
#1
2
Something like:
Sub poiuyt()
Range("M19:M20").Formula = "=IFNA(INDEX('beach'!B:B,MATCH(F19,'beach'!$G:$G,0)),0)"
End Sub
will auto-adjust the F index:
将自动调整F指数:
#2
1
You can also use R1C1
style.
您也可以使用R1C1风格。
Say you want that formula to apply in range M19:M20
with your F19
reference updating to each row:
假设您希望该公式适用于范围M19:M20,并且您的F19参考更新到每一行:
Range("M19:M20").FormulaR1C1 = "=IFNA(INDEX(beach!C[-11],MATCH(RC[-7],beach!C7,0)),0)"