包括的功能有:创建新表,新表里面插入数据,循环,判断等
Sub aa()
'获取当前页的店名及金额信息
Dim beginIndex
Dim sheetName
Dim totalMoney
Dim sheetLB As Excel.Worksheet
Set sheetLB = Application.Worksheets("列表")
beginIndex = 2 '从第2行开始算起
'总店信息 数据从第5行开始
Dim sheetYM As Excel.Worksheet
Set sheetYM = Application.Worksheets("库存汇总")
Dim ymIndex
ymIndex = 5
'生成的新表title 7列数据
Dim A, B, C, D, E, F, G
A = "存货名称" '总店第1列
B = "规格型号" '总店第2列
C = "单位" '总店第3列
D = "类别" '总店第4列
E = "数量" '总店第14列
F = "单价" '总店第15列
G = "金额" '总店第16列
Do While (sheetLB.Cells(beginIndex, 1) <> "")
ymIndex = 5 '总店从第5行开始
sheetName = sheetLB.Cells(beginIndex, 1)
totalMoney = sheetLB.Cells(beginIndex, 2)
'创建一个新表
Set NewSheet = Worksheets.Add
NewSheet.Name = sheetName
'设置活动工作表表头
NewSheet.Cells(1, 1) = A '1
NewSheet.Cells(1, 2) = B '2
NewSheet.Cells(1, 3) = C '3
NewSheet.Cells(1, 4) = D '4
NewSheet.Cells(1, 5) = E '14
NewSheet.Cells(1, 6) = F '15
NewSheet.Cells(1, 7) = G '16
'循环开始,注意totalMoney为界限
Dim startInsert
startInsert = 2 '从第2行开始插入数据
Do While (totalMoney - 100 > 0)
'如果库存数量大于0
If sheetYM.Cells(ymIndex, 14) > 0 Then
'开始写数据 数量 单价 限定值
Dim num, price, max
num = Int(sheetYM.Cells(ymIndex, 14))
price = sheetYM.Cells(ymIndex, 15)
max = Int(totalMoney / price)
If num >= max Then
num = max
Else
'生成随机数字
num = Int(Rnd * (num)) + 1
End If
sheetYM.Cells(ymIndex, 14) = sheetYM.Cells(ymIndex, 14) - num '减掉库存数量
sheetYM.Cells(ymIndex, 16) = sheetYM.Cells(ymIndex, 14) * price '重新计算库存总额
'插入数据
NewSheet.Cells(startInsert, 1) = sheetYM.Cells(ymIndex, 1) '1
NewSheet.Cells(startInsert, 2) = sheetYM.Cells(ymIndex, 2) '2
NewSheet.Cells(startInsert, 3) = sheetYM.Cells(ymIndex, 3) '3
NewSheet.Cells(startInsert, 4) = sheetYM.Cells(ymIndex, 4) '4
NewSheet.Cells(startInsert, 5) = num '14
NewSheet.Cells(startInsert, 6) = price '15
NewSheet.Cells(startInsert, 7) = num * price '16
'减少总额
totalMoney = totalMoney - num * price
startInsert = startInsert + 1 '新增一行
End If
ymIndex = ymIndex + 1 '总店循环下一列
'如果总店循环完毕了,跳出循环
If sheetYM.Cells(ymIndex, 1) = "" Then
'注意这里如果不够是否需要进行提醒???????????????????
Exit Do
End If
Loop
sheetLB.Cells(beginIndex, 3) = "完成"
beginIndex = beginIndex + 1
Loop
End Sub