求助大神，结果要求按列填报，条件是按行列示的，如何先找到结果在条件中的行位置

tx_888

哪位大神能帮帮我。我想先在“条件”页签中最上面一行中找到各个产品的所在列位置，然后统计该列全部非空单元格对应的金额合计，并填到“结果”页签的相应位置。

3190496160

Sub 汇总()
Dim ar As Variant, cr As Variant
Dim i As Long, r As Long
Dim br()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("情况")
    r = .Cells(Rows.Count, 1).End(xlUp).Row
    If r < 2 Then MsgBox "情况工作表为空！": End
   ar = .Range("a1:e" & r)
End With
ReDim br(1 To UBound(ar, 2), 1 To 2)
br(1, 1) = "产品名称"
br(1, 2) = "金额合计"
For j = 2 To UBound(ar, 2)
    br(j, 1) = ar(1, j)
    For i = 2 To UBound(ar)
        If ar(i, j) <> "" Then
            br(j, 2) = br(j, 2) + ar(i, 1)
        End If
    Next i
Next j
With Sheets("结果")
    .[a1].CurrentRegion = Empty
    .[a1].Resize(UBound(br), UBound(br, 2)) = br
End With
MsgBox "ok!"
End Sub

3190496160

求助.rar (17.9 KB, 下载次数: 0)

2024-9-18 08:45 上传

点击文件名下载附件

tx_888

3190496160 发表于 2024-9-18 08:45

感谢帮助

tx_888

3190496160 发表于 2024-9-18 08:43
Sub 汇总()
Dim ar As Variant, cr As Variant
Dim i As Long, r As Long

感谢帮助
这是vba？

yfan17

1. =SUMIF(OFFSET(情况!$A:$A,,MATCH($A2,情况!$1:$1,)-1),"<>",情况!$A:$A)

复制代码

=12683.42