excel进销存使用说明及代码.docx
- 文档编号:10307365
- 上传时间:2023-02-10
- 格式:DOCX
- 页数:17
- 大小:241.55KB
excel进销存使用说明及代码.docx
《excel进销存使用说明及代码.docx》由会员分享,可在线阅读,更多相关《excel进销存使用说明及代码.docx(17页珍藏版)》请在冰豆网上搜索。
excel进销存使用说明及代码
excel进销存使用说明.doc
一、初始密码:
999
二、启用宏,见下图。
1
2
三、清除无效内容
四、输入内容
可以在表格中直接输入,也可以在输入框中输入后,单击新增品种按钮。
‘代码:
PrivateSubCommandButton1_Click()
'查看下一个
DimiAsInteger
Fori=Range("i4")To1224
IfRange("a"&i)=""ThenMsgBox"没找到":
GoTo50
IfRange("a"&i)=mingcheng.TextThen
Range("a"&i).Select
Rows(i&":
"&i).Select
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
shuliang.Text=Range("e"&i)
leibie.Text=Range("d"&i)
danjia.Text=Range("f"&i)
jine.Text=Range("g"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
Range("i4")=i+1
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo50
EndIf
Next
MsgBox"找到了!
"
'Range("b8").Select
'
50
EndSub
PrivateSubCommandButton10_Click()
Range("A10001:
G10299").Select
Selection.ClearContents
Range("A10001:
G10305").Select
Selection.DeleteShift:
=xlUp
Range("G10000").Select
EndSub
PrivateSubCommandButton2_Click()
'查看
DimiAsInteger
Fori=3To1224
IfRange("a"&i)=""ThenMsgBox"没找到":
GoTo50
IfRange("a"&i)=mingcheng.TextThen
Range("a"&i).Select
Rows(i&":
"&i).Select
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
shuliang.Text=Range("e"&i)
leibie.Text=Range("d"&i)
danjia.Text=Range("f"&i)
jine.Text=Range("g"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
Range("i4")=i+1
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo50
EndIf
Next
MsgBox"找到了!
"
'Range("b8").Select
'
50
EndSub
PrivateSubCommandButton3_Click()
'新增进货
DimiAsInteger
i=Range("i4")-1
Range("e"&i)=Range("e"&i)+shuliang.Text
Range("f"&i)=danjia.Text
'显示更新结果
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
shuliang.Text=Range("e"&i)
leibie.Text=Range("d"&i)
danjia.Text=Range("f"&i)
jine.Text=Range("g"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
EndSub
PrivateSubCommandButton4_Click()
'修改进货
DimiAsInteger
i=Range("i4")-1
Range("e"&i)=shuliang.Text
Range("f"&i)=danjia.Text
'显示更新结果
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
shuliang.Text=Range("e"&i)
leibie.Text=Range("d"&i)
danjia.Text=Range("f"&i)
jine.Text=Range("g"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
EndSub
PrivateSubCommandButton5_Click()
'岀货
DimiAsInteger
Fori=10000To11000
IfRange("a"&i)=""Then
Range("a"&i)=mingcheng.Text
Range("b"&i)=guige.Text
Range("c"&i)=bianma.Text
Range("e"&i)=xdanjia.Text
Range("d"&i)=xshuliang.Text
Range("f"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
'Range("g"&i)=jine.Text
'Range("j"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
Range("A9996:
G"&i).Select
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo110
EndIf
Next
110
'''''''''''''''''''''''''''''''''''''''''''''''''''''
i=Range("i4")-1
Range("j"&i)=Range("j"&i)+xdanjia.Text*xshuliang.Text'xjine.Text
Range("h"&i)=Range("h"&i)+xshuliang.Text
Range("i"&i)=xdanjia.Text
'显示更新结果
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
'Sheets("存货").Select
i=Range("i4")-1
Range("j"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
Range("h"&i)=xshuliang.Text
Range("i"&i)=xdanjia.Text
'''''''''''''''''''''''''''''''''''''''''''''
EndSub
PrivateSubCommandButton6_Click()
'新增品种
DimiAsInteger
Fori=3To1224
IfRange("a"&i)=""Then
Range("a"&i)=mingcheng.Text
Range("b"&i)=guige.Text
Range("c"&i)=bianma.Text
Range("e"&i)=shuliang.Text
Range("d"&i)=leibie.Text
Range("f"&i)=danjia.Text
'Range("g"&i)=jine.Text
Range("h"&i)=xshuliang.Text
Range("i"&i)=xdanjia.Text
Range("j"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
'显示更新结果
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
guige.Text=Range("b"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
bianma.Text=Range("c"&i)
xshuliang.Text=Range("h"&i)
leibie.Text=Range("d"&i)
xdanjia.Text=Range("i"&i)
xjine.Text=Range("j"&i)
cshuliang.Text=Range("k"&i)
cjine.Text=Range("l"&i)
Range("a"&i).Select
Rows(i&":
"&i).Select
Range("i4")=i+1
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo100
EndIf
Next
100
EndSub
PrivateSubCommandButton7_Click()
'清空
mingcheng.Text=""
guige.Text=""
bianma.Text=""
shuliang.Text=""
leibie.Text=""
danjia.Text=""
jine.Text=""
cshuliang.Text=""
cjine.Text=""
guige.Text=""
bianma.Text=""
xshuliang.Text=""
leibie.Text=""
xdanjia.Text=""
xjine.Text=""
cshuliang.Text=""
cjine.Text=""
bianma.Text=""
xshuliang.Text=""
leibie.Text=""
xdanjia.Text=""
xjine.Text=""
cshuliang.Text=""
cjine.Text=""
EndSub
PrivateSubCommandButton8_Click()
'山货完毕
DimiAsInteger
Range("b"&9998)=InputBox("","输入客户名称")
Range("f"&9998)=InputBox("","输入销售单号")
Fori=10000To11000
IfRange("a"&i)=""Then
Range("a"&i)="合计:
"
Range("f"&i)=0
Forj=10001Toi-1
Range("f"&i)=Range("f"&i)+Range("f"&j)
Nextj
Range("c"&i+2)="制表人:
"
Range("e"&i+2)=""
Range("d"&i+2)=InputBox("","输入制表人姓名")
Range("f"&i+2)=日期:
'xjine.Text
Range("g"&i+2)=Date
'Range("j"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
Range("A9996:
G"&i+2).Select
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo110
EndIf
Next
110
EndSub
PrivateSubCommandButton9_Click()
'打印货单
DimiAsInteger
Fori=10000To11000
IfRange("a"&i)=""Then
'''''''''''''''''''''''''''''''''''''''''
Range("A10000:
G"&i).Select
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
WithSelection.Borders(xlEdgeLeft)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeRight)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlInsideVertical)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
'‘’‘’‘’‘’‘’‘’‘
Range("A"&i+1&":
G"&i+1).Select
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
Selection.Borders(xlEdgeLeft).LineStyle=xlNone
WithSelection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.Weight=xlThin
.ColorIndex=xlAutomatic
EndWith
Selection.Borders(xlEdgeBottom).LineStyle=xlNone
Selection.Borders(xlEdgeRight).LineStyle=xlNone
Selection.Borders(xlInsideVertical).LineStyle=xlNone
Range("G10009").Select
''''''''''''''''''''''''''''''''''
'Range("j"&i)=xdanjia.Text*xshuliang.Text'xjine.Text
Range("A9996:
G"&i+1).Select
Selection.PrintOutCopies:
=1,Preview:
=True,Collate:
=True
'ActiveWorkbook.Save
'ActiveWindow.SelectedSheets.PrintPreview
'Selection.PrintOutCopies:
=1,Collate:
=True
'Range("b"&i)=""
'Range("c"&i)=""
'Range("D"&i)=""
'Range("E"&i)="ok"
GoTo120
EndIf
Next
120
EndSub
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 进销存 使用说明 代码
![提示](https://static.bdocx.com/images/bang_tan.gif)