如何修复以下内容以使其顺序?

时间:2022-10-30 09:32:35

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  

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)"

#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)"