VBA，如何将动态/相对单元格引用插入到 .formulaArray 方法中？

VBA, how to insert a dynamic / relative cell reference into a .formulaArray method?

提问by eMTy

I have the following code:

我有以下代码：

  With Sh.Range("A1:A" & LastRow + 1).Offset(0, 4)
    .FormulaArray = "=MAX(IF(A2:A" & LastRow + 1 & "= ** , D2:D" & LastRow + 1 & "))"
    .Value = .Value
  End With

In the place where I have **, I would want a dynamic cell reference. If I was using .formulaR1C1, I would have inserted RC[-1], but I can't use that with a .formulaArray.

在我有 ** 的地方，我想要一个动态单元格引用。如果我使用 .formulaR1C1，我会插入 RC[-1]，但我不能将它与 .formulaArray 一起使用。

Does anyone know how I can insert a relative cell reference that would change as the formula being pasted within the range?

有谁知道我如何插入一个相对单元格引用，该引用会随着公式在范围内粘贴而发生变化？

Thank you

谢谢

EDIT # 1

编辑＃1

The whole code looks like this:

整个代码如下所示：

Sub RemoveDuplicates_SumMarketValue()
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range



Set Sh = Worksheets(1)

Sh.Columns(6).Insert

LastRow = Sh.Range("A65536").End(xlUp).Row

With Sh.Range("A1:A" & LastRow).Offset(0, 5)
    .FormulaR1C1 = "=IF(COUNTIF(R1C[-5]:RC[-5],RC[-5])>1,"""",SUMIF(R1C[-5]:R[" & LastRow & "]C[-5],RC[-5],R1C[-1]:R[" & LastRow & "]C[-1]))"
    .Value = .Value
End With
Sh.Columns(5).Delete
Sh.Rows(1).Insert



Sh.Columns(5).Insert

With Sh.Range("A1:A" & LastRow + 1).Offset(0, 4)
    .FormulaArray = "=MAX(IF(A2:A" & LastRow + 1 & "= A1 , D2:D" & LastRow + 1 & "))"
    .Value = .Value
End With



Set Rng = Sh.Range("E1:E" & LastRow + 1)

With Rng
    .AutoFilter Field:=1, Criteria1:="="
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With

End Sub

This purpose of this code, is too look though a sample of data and

这段代码的这个目的，太看不起数据样本了

1. find duplicates
2. sum up values in 5th column associated with duplicates
3. remove duplicate rows (except the one that carries the sum from 5th column)

1. 查找重复项
2. 总结与重复相关的第 5 列中的值
3. 删除重复的行（除了第 5 列的总和）

Now I also want it to have the max value from column 4th of all the duplicates to be retained in the final version, but I can't get the array formula to reference the row correctly.

现在我还希望它在最终版本中保留所有重复项的第 4 列的最大值，但我无法让数组公式正确引用该行。

回答by Bernard Saucier

EDIT :Try pasting this inside the "ThisWorkbook" code sheet :

编辑：尝试将其粘贴到“ThisWorkbook”代码表中：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range

Set Sh = Worksheets(1)

LastRow = Sh.Range("A65536").End(xlUp).Row

With Sh.Range("A1:A" & LastRow).Offset(0, 5)
    .FormulaR1C1 = "=IF(COUNTIF(R1C[-5]:RC[-5],RC[-5])>1,"""",SUMIF(R1C[-5]:R[" & LastRow & "]C[-5],RC[-5],R1C[-1]:R[" & LastRow & "]C[-1]))"
    .Value = .Value
End With

With Sh.Range("A1:A" & LastRow + 1).Offset(0, 4)
    .FormulaArray = "=MAX(IF(A2:A" & LastRow + 1 & "= A1 , D2:D" & LastRow + 1 & "))"
    .Value = .Value
End With

'This section you might want to remove from this routine
Set Rng = Sh.Range("E1:E" & LastRow + 1)

With Rng
    .AutoFilter Field:=1, Criteria1:="="
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

End Sub

It basically is the same as your function, but it doesn't remove the columns or add any rows. What will happen is everytime one of your cell's content changes, this macro will run automatically, updating the formulas in the cells.

它基本上与您的函数相同，但它不会删除列或添加任何行。每次单元格的内容发生变化时，该宏都会自动运行，更新单元格中的公式。

---

---

The closest you can get to achieving that, is having a macro in the background that will be running everytime a change is made to the sheet. If you have tens of thousands of rows, or a REALLY slow computer, this may not be the ideal solution. If this is not the case, however, you may find it very easy to get your code to work with very little changes.

最接近实现这一目标的是在后台运行一个宏，每次对工作表进行更改时都会运行该宏。如果您有数万行，或者计算机速度非常慢，这可能不是理想的解决方案。但是，如果不是这种情况，您可能会发现只需很少的更改就可以很容易地使您的代码工作。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'Update your formula here with the new content/reference 
    '  (your code + some changes to update where the last row is)

End Sub

Paste this inside the "ThisWorkbook", and simply place your code inside it.

将其粘贴到“ThisWorkbook”中，然后将您的代码放入其中。

回答by eMTy

This is what I came up with to solve the issue of the .formulaArray not accepting RC cell reference notation. I just used a loop to insert the array formula into each cell and reference the target row by using the loop variable i.

这就是我想出的解决 .formulaArray 不接受 RC 单元格引用符号的问题的方法。我只是使用循环将数组公式插入每个单元格并使用循环变量 i 引用目标行。

Code:

代码：

Sub RemoveDuplicates_SumMarketValue()
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Dim targetcell As Range




Set Sh = Worksheets(1)

Sh.Columns(6).Insert

LastRow = Sh.Range("A65536").End(xlUp).Row

With Sh.Range("A1:A" & LastRow).Offset(0, 5)
    .FormulaR1C1 = "=IF(COUNTIF(R1C[-5]:RC[-5],RC[-5])>1,"""",SUMIF(R1C[-5]:R[" & LastRow & "]C[-5],RC[-5],R1C[-1]:R[" & LastRow & "]C[-1]))"
    .Value = .Value
End With
Sh.Columns(5).Delete
Sh.Rows(1).Insert



Sh.Columns(5).Insert

For i = 2 To LastRow + 1


Cells(i, 5).FormulaArray = "=MAX(IF(A2:A" & LastRow + 1 & "= A" & i & " , D2:D" & LastRow + 1 & "))"
Cells(i, 5) = Cells(i, 5).Value

Next
Sh.Columns(4).Delete


Set Rng = Sh.Range("E1:E" & LastRow + 1)

With Rng
    .AutoFilter Field:=1, Criteria1:="="
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With

Sheets(1).Cells(1, 4) = "Price" Sheets(1).Cells(1, 5) = "market value"

Sheets(1).Cells(1, 4) = "价格" Sheets(1).Cells(1, 5) = "市场价值"

End Sub

结束子

So what this code does, loops for duplicates in Col 1, sums up associated values in col 5 and picks the max associated value in col 4.

所以这段代码的作用是，循环第 1 列中的重复项，总结第 5 列中的关联值，并选择第 4 列中的最大关联值。

回答by Atif Hussain

Could you use, where you fill a cell and replicate it,

你可以使用，在你填充一个单元格并复制它的地方，

L = LastRow + 1
With Sh.Range("A1:A" & L).Offset(0, 4)
  .Cells(1,1).FormulaArray = "=MAX(IF(A:A$" & L & "=A1,D:D$" & L & "))"
  .FillDown
  .Value = .Value
End With

Handling A1 vs R1C1 style is easy, with Application.ConvertFormula

使用 Application.ConvertFormula 处理 A1 与 R1C1 风格很容易

Need to be careful about Row/Col Abs/Rel referencing.

需要注意 Row/Col Abs/Rel 引用。