vba在Excel中使用sumif公式查找对应项

Public Sub 条件求和()

For han = 1 To Sheets("sheet2").UsedRange.Rows.Count

xingming = Sheets("sheet2").Cells(han, 1)

shuju = 0
For y = 1 To Sheets("sheet1").UsedRange.Rows.Count
xingming1 = Sheets("sheet1").Cells(y, 1)

If xingming = xingming1 Then
shuju = shuju + Sheets("sheet1").Cells(y, 2)
End If

Next y

Sheets("sheet2").Cells(han, 2) = shuju
Next han
End Sub

如果显示结果在SHEET2工作表的B列，就用下面代码
For a = 1 To Sheets("sheet2").UsedRange.Rows.Count
Sheets("sheet2").Cells(a, 2) = Application.WorksheetFunction.SumIf(Sheets1.Range("A:A"), Cells(a, 1), Sheets("sheet2").Range("B:B"))
Next
这个代码中“ Cells(a, 1)”表示第A行第1列

i = Sheets("sheet1").UsedRange.Rows.Count 'i为sheet1中的有效行数
For a = 1 To Sheets("sheet2").UsedRange.Rows.Count
Sheets("sheet2").Cells(a, 2) = "=SUMIF(Sheet1!R1C1:R" & i & "C1,RC[-1],Sheet1!R1C2:R" & i & "C2)"
Next

Sheets("sheet2").Cells(b, 2).value =Application.WorksheetFunction.SumIf(Sheets1.Range("B:B"), Sheets("AutoRun").Range("A2"), Sheets1Range("B:B"))