VBA使用公式的一部分作为单元格值

Question

我的项目再次失败

我有可变品牌的公式，它是动态变化的(AF列(。基本上，我只想把品牌提取到公式列旁边的一列(AE(中，以方便视觉

For i = LBound(Brand) To UBound(Brand)
Range("AF" & i + 2).Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & _
",H:H,""Headphones"",F:F," & Chr(34) & Brand(i) & Chr(34) & ")"
Next i
Range("AF:AF").Sort Key1:=Range("AF2"), Order1:=xlDescending, Header:=xlYes
ActiveSheet.Range("AG2:AG8").Formula = ActiveSheet.Range("AF2:AF8").Formula
ActiveSheet.Range("AH2:AH8").Formula = ActiveSheet.Range("AF2:AF8").Formula
Dim ws As Worksheet
Set ws = Worksheets(1)
Dim j As Variant
j = Application.Match(""" & Brand(i) & """, ws.Range("AF2:AF8"))
ActiveSheet.Range("AE2").Value = Application.Index(ws.Range("AF2:AF8"), j, 0)

我得到#N/A已经为此损失了两天。我会非常感谢任何能提供帮助的人。

Answer 1

从你的问题中还不清楚你想要的输出，但这里有一个猜测：

For i = LBound(Brand) To UBound(Brand)
Range("AF" & i + 2).Formula = "=COUNTIFS(C:C," & RTrim(Month(Mesyaz3)) & _
",H:H,""Headphones"",F:F," & Chr(34) & Brand(i) & Chr(34) & ")"
Range("AE" & i + 2).Value = Brand(i)
Next i
Range("AE:AF").Sort Key1:=Range("AF2"), Order1:=xlDescending, Header:=xlYes

我添加了一行将品牌写入AE，并修改了Sort以适应这一点。