求优化此段代码

shenglei635

Sub 库产报包()
Dim a, i
a = Sheet1.Range("a100000").End(xlUp).Row
For i = 3 To a
Sheet1.Range("d" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("e2:e100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麸皮*")
Sheet1.Range("e" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("f2:f100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麸皮*")
Sheet1.Range("f" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("g2:g100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麸皮*")
Sheet1.Range("v" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("e2:e100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*糠麸*")
Sheet1.Range("w" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("f2:f100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*糠麸*")
Sheet1.Range("x" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("g2:g100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*糠麸*")
Sheet1.Range("an" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("e2:e100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麦胚*")
Sheet1.Range("ao" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("f2:f100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麦胚*")
Sheet1.Range("ap" & i).Value = Application.WorksheetFunction.SumIfs(Sheet3.Range("g2:g100000"), Sheet3.Range("b2:b100000"), Sheet1.Range("a" & i), Sheet3.Range("d2:d100000"), "*麦胚*")
Sheet1.Range("c" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("b3:b100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("u" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("c3:c100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("am" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("d3:d100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("g" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("e3:e100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("y" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("f3:f100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("aq" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("g3:g100000"), Sheet2.Range("a3:a100000"), Sheet1.Range("a" & i))
Sheet1.Range("h" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("q3:q100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*麸皮*")
Sheet1.Range("z" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("q3:q100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*糠麸*")
Sheet1.Range("ar" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("q3:q100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*麦胚*")
Sheet1.Range("i" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("s3:s100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*麸皮*")
Sheet1.Range("aa" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("s3:s100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*糠麸*")
Sheet1.Range("as" & i).Value = Application.WorksheetFunction.SumIfs(Sheet2.Range("s3:s100000"), Sheet2.Range("n3:n100000"), Sheet1.Range("a" & i), Sheet2.Range("p3:p100000"), "*麦胚*")
Sheet1.Range("b" & 1 + i).Value = (Sheet1.Range("b" & i) + Sheet1.Range("c" & i) - Sheet1.Range("d" & i) - Range("h" & i))
Sheet1.Range("t" & 1 + i).Value = (Sheet1.Range("t" & i) + Sheet1.Range("u" & i) - Sheet1.Range("v" & i) - Range("z" & i))
Sheet1.Range("al" & 1 + i).Value = (Sheet1.Range("al" & i) + Sheet1.Range("am" & i) - Sheet1.Range("an" & i) - Range("ar" & i))
Next
End Sub

lygkkk

用with简化

shenglei635

lygkkk 发表于 2016-1-1 22:30
用with简化

怎么做？请帮忙

lygkkk

唉~~模版丢上来 写上要求 直接帮你写一份代码吧

fire9999456

同意楼上的说法，你把文件传上来