ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 Office知识技巧免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 391|回复: 8

VBA代码运行慢,请教大神指导!急!!1

[复制链接]

TA的精华主题

TA的得分主题

发表于 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


TA的精华主题

TA的得分主题

 楼主| 发表于 2019-9-9 11:41 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2019-9-9 11:43 | 显示全部楼层
呃……功能说明下,附件发上来
都用VBA了,还每个单元格写VLOOKUP干啥
然后还有你定义个变量i加个循环起什么作用?

TA的精华主题

TA的得分主题

发表于 2019-9-9 11:44 | 显示全部楼层

TA的精华主题

TA的得分主题

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

好的,我是菜鸟,请指教!附件发出来,请看看
头像被屏蔽

TA的精华主题

TA的得分主题

发表于 2019-9-9 11:56 来自手机 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2019-9-9 11:57 | 显示全部楼层

TA的精华主题

TA的得分主题

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

附件上传不了,太大,请指导一下!

TA的精华主题

TA的得分主题

发表于 2019-9-9 14:08 | 显示全部楼层
takinfu2019 发表于 2019-9-9 11:58
附件上传不了,太大,请指导一下!

这怎么指导哦,你了解下循环和条件判断该怎么用了就明白了,for……next和if……end if
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2020-3-30 21:52 , Processed in 0.155155 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表