VBA代码运行慢，请教大神指导！急！！1

takinfu2019

Sub 【1】采购订单生成()
Application.ScreenUpdating = False
On Error Resume Next
Dim i As Integer
For i = 2 To [E65536].End(xlUp).Row
Cells(9, 8) = WorksheetFunction.VLookup(Cells(9, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(9, 9) = WorksheetFunction.VLookup(Cells(9, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(9, 10) = WorksheetFunction.VLookup(Cells(9, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(9, 12) = WorksheetFunction.VLookup(Cells(9, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(9, 15) = VBA.IIf(Cells(9, 14) = "", "", Cells(9, 13) * Cells(9, 14))

Cells(10, 8) = WorksheetFunction.VLookup(Cells(10, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(10, 9) = WorksheetFunction.VLookup(Cells(10, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(10, 10) = WorksheetFunction.VLookup(Cells(10, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(10, 12) = WorksheetFunction.VLookup(Cells(10, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(10, 15) = VBA.IIf(Cells(10, 14) = "", "", Cells(10, 13) * Cells(10, 14))

Cells(11, 8) = WorksheetFunction.VLookup(Cells(11, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(11, 9) = WorksheetFunction.VLookup(Cells(11, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(11, 10) = WorksheetFunction.VLookup(Cells(11, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(11, 12) = WorksheetFunction.VLookup(Cells(11, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(11, 15) = VBA.IIf(Cells(11, 14) = "", "", Cells(11, 13) * Cells(11, 14))


Cells(12, 8) = WorksheetFunction.VLookup(Cells(12, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(12, 9) = WorksheetFunction.VLookup(Cells(12, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(12, 10) = WorksheetFunction.VLookup(Cells(12, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(12, 12) = WorksheetFunction.VLookup(Cells(12, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(12, 15) = VBA.IIf(Cells(12, 14) = "", "", Cells(12, 13) * Cells(12, 14))

Cells(13, 8) = WorksheetFunction.VLookup(Cells(13, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(13, 9) = WorksheetFunction.VLookup(Cells(13, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(13, 10) = WorksheetFunction.VLookup(Cells(13, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(13, 12) = WorksheetFunction.VLookup(Cells(13, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(13, 15) = VBA.IIf(Cells(13, 14) = "", "", Cells(13, 13) * Cells(13, 14))

Cells(14, 8) = WorksheetFunction.VLookup(Cells(14, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(14, 9) = WorksheetFunction.VLookup(Cells(14, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(14, 10) = WorksheetFunction.VLookup(Cells(14, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(14, 12) = WorksheetFunction.VLookup(Cells(14, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(14, 15) = VBA.IIf(Cells(14, 14) = "", "", Cells(14, 13) * Cells(14, 14))

Cells(15, 8) = WorksheetFunction.VLookup(Cells(15, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(15, 9) = WorksheetFunction.VLookup(Cells(15, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(15, 10) = WorksheetFunction.VLookup(Cells(15, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(15, 12) = WorksheetFunction.VLookup(Cells(15, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(15, 15) = VBA.IIf(Cells(15, 14) = "", "", Cells(15, 13) * Cells(14, 14))

Cells(16, 8) = WorksheetFunction.VLookup(Cells(16, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(16, 9) = WorksheetFunction.VLookup(Cells(16, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(16, 10) = WorksheetFunction.VLookup(Cells(16, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(16, 12) = WorksheetFunction.VLookup(Cells(16, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(16, 15) = VBA.IIf(Cells(16, 14) = "", "", Cells(16, 13) * Cells(16, 14))


Cells(17, 8) = WorksheetFunction.VLookup(Cells(17, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(17, 9) = WorksheetFunction.VLookup(Cells(17, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(17, 10) = WorksheetFunction.VLookup(Cells(17, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(17, 12) = WorksheetFunction.VLookup(Cells(17, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(17, 15) = VBA.IIf(Cells(17, 14) = "", "", Cells(17, 13) * Cells(17, 14))


Cells(18, 8) = WorksheetFunction.VLookup(Cells(18, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(18, 9) = WorksheetFunction.VLookup(Cells(18, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(18, 10) = WorksheetFunction.VLookup(Cells(18, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(18, 12) = WorksheetFunction.VLookup(Cells(18, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(18, 15) = VBA.IIf(Cells(18, 14) = "", "", Cells(18, 13) * Cells(18, 14))


Cells(19, 8) = WorksheetFunction.VLookup(Cells(19, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(19, 9) = WorksheetFunction.VLookup(Cells(19, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(19, 10) = WorksheetFunction.VLookup(Cells(19, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(19, 12) = WorksheetFunction.VLookup(Cells(19, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(19, 15) = VBA.IIf(Cells(19, 14) = "", "", Cells(19, 13) * Cells(19, 14))


Cells(20, 8) = WorksheetFunction.VLookup(Cells(20, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(20, 9) = WorksheetFunction.VLookup(Cells(20, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(20, 10) = WorksheetFunction.VLookup(Cells(20, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(20, 12) = WorksheetFunction.VLookup(Cells(20, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(20, 15) = VBA.IIf(Cells(20, 14) = "", "", Cells(20, 13) * Cells(20, 14))


Cells(21, 8) = WorksheetFunction.VLookup(Cells(21, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(21, 9) = WorksheetFunction.VLookup(Cells(21, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(21, 10) = WorksheetFunction.VLookup(Cells(21, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(21, 12) = WorksheetFunction.VLookup(Cells(21, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(21, 15) = VBA.IIf(Cells(21, 14) = "", "", Cells(21, 13) * Cells(21, 14))


Cells(22, 8) = WorksheetFunction.VLookup(Cells(22, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(22, 9) = WorksheetFunction.VLookup(Cells(22, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(22, 10) = WorksheetFunction.VLookup(Cells(22, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(22, 12) = WorksheetFunction.VLookup(Cells(22, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(22, 15) = VBA.IIf(Cells(22, 14) = "", "", Cells(22, 13) * Cells(22, 14))

Cells(23, 8) = WorksheetFunction.VLookup(Cells(23, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(23, 9) = WorksheetFunction.VLookup(Cells(23, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(23, 10) = WorksheetFunction.VLookup(Cells(23, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(23, 12) = WorksheetFunction.VLookup(Cells(23, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(23, 15) = VBA.IIf(Cells(23, 14) = "", "", Cells(23, 13) * Cells(23, 14))

Cells(24, 8) = WorksheetFunction.VLookup(Cells(24, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(24, 9) = WorksheetFunction.VLookup(Cells(24, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(24, 10) = WorksheetFunction.VLookup(Cells(24, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(24, 12) = WorksheetFunction.VLookup(Cells(24, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(24, 15) = VBA.IIf(Cells(24, 14) = "", "", Cells(24, 13) * Cells(24, 14))


Cells(25, 8) = WorksheetFunction.VLookup(Cells(25, 5), Sheets("商品信息").Range("E4:H5003"), 4, 0)
Cells(25, 9) = WorksheetFunction.VLookup(Cells(25, 5), Sheets("商品信息").Range("E4:I5003"), 5, 0)
Cells(25, 10) = WorksheetFunction.VLookup(Cells(25, 5), Sheets("商品信息").Range("E4:J5003"), 6, 0)
Cells(25, 12) = WorksheetFunction.VLookup(Cells(25, 5), Sheets("商品信息").Range("E4:L5003"), 8, 0)
Cells(25, 15) = VBA.IIf(Cells(25, 14) = "", "", Cells(25, 13) * Cells(25, 14))



Application.ScreenUpdating = True


Next
End Sub

takinfu2019

求高手指教！！！

lh123lh1234

呃……功能说明下，附件发上来
都用VBA了，还每个单元格写VLOOKUP干啥
然后还有你定义个变量i加个循环起什么作用？

lh123lh1234

查找用循环就可以了

takinfu2019

lh123lh1234 发表于 2019-9-9 11:43
呃……功能说明下，附件发上来
都用VBA了，还每个单元格写VLOOKUP干啥
然后还有你定义个变量i加个循环起 ...

好的，我是菜鸟，请指教！附件发出来，请看看

lh123lh1234

takinfu2019 发表于 2019-9-9 11:46
好的，我是菜鸟，请指教！附件发出来，请看看

没看到附件呀

takinfu2019

lh123lh1234 发表于 2019-9-9 11:43
呃……功能说明下，附件发上来
都用VBA了，还每个单元格写VLOOKUP干啥
然后还有你定义个变量i加个循环起 ...

附件上传不了，太大，请指导一下！

lh123lh1234

takinfu2019 发表于 2019-9-9 11:58
附件上传不了，太大，请指导一下！

这怎么指导哦，你了解下循环和条件判断该怎么用了就明白了，for……next和if……end if