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

takinfu2019 · 发表于 2019-9-9 11:39

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 · 发表于 2019-9-9 11:41

求高手指教！！！

lh123lh1234 · 发表于 2019-9-9 11:43

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

lh123lh1234 · 发表于 2019-9-9 11:44

查找用循环就可以了

takinfu2019 · 发表于 2019-9-9 11:46

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

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

朱荣兴 · 发表于 2019-9-9 11:56

提示: 作者被禁止或删除内容自动屏蔽

lh123lh1234 · 发表于 2019-9-9 11:57

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

没看到附件呀

takinfu2019 · 发表于 2019-9-9 11:58

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

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

lh123lh1234 · 发表于 2019-9-9 14:08

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

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

		自动登录	找回密码
密码			免费注册

朱荣兴朱荣兴当前离线性别保密 UID 1318548 阅读权限 0 注册时间 2011-2-2 在线时间小时最后登录 1970-1-1 积分 19282 财富分享技术精华头像被屏蔽 TA的精华主题 TA的得分主题	发表于 2019-9-9 11:56 来自手机 \| 显示全部楼层 [广告] ★ ReportLite:表哥表姐早下班神器，专治各种报表不服，拒绝无意义的重复劳动 ★ 提示: 作者被禁止或删除内容自动屏蔽

	回复举报