10年VBA经验，闲着没事找事干；你出题，我来做，闲着也是闲着

Betty201005

高手无处不在,学习不断向前!

amos7839

做个小程序，需要在两个支持VBA的软件之间来回操作，不知道只用VBA能不能实现。具体过程如下：在AutoCAD里点击菜单，使用VBA启动Excel，然后在Excel中打开或新建文件并编辑，完成后选择部分数据再点击Excel中的菜单或命令按钮使用VBA返回到AutoCad中绘图。希望能给出一些指导，或者推荐几本参考书，谢谢！

ae9480

请教老师：这是一个23行自动分页小计累计的代码，假若要改为25行和30行自动分页小计，要改那个值？谢谢！
Sub a增加小计()
Do
bt = Application.InputBox(prompt:="请输入要求和的列，用数字表示。" & Chr(13) & "如D列则输入4，多列用半角“,”分开：", Title:="输入需要求和的列")
If bt = False Then Exit Sub
bt = bt & ","
For k = 1 To Len(bt) - 1
b = Mid(bt, k, WorksheetFunction.Find(",", bt, k) - k)
If b > 1 And b < 257 Then
Else
MsgBox "输入列数格式错误，请重新输入！", vbOKOnly, "错误"
Exit For
End If
k = k + Len(b)
Next k
Loop Until k > Len(bt)
k = 0
Do
k = k + 1
If k > 50 Then MsgBox "未找到起始行。（A列序号为1的那行）", vbOKOnly, "失败": Exit Sub
Loop Until Cells(k, 1) = 1
Application.ScreenUpdating = False
For j = 4 To Range("A65536").End(xlUp).Row
If Cells(j, 1) = "本页小计" Or Cells(j, 1) = "累计" Or Cells(j, 1) = "总计" Then
Rows(j).Delete shift:=xlUp
j = j - 1
End If
Next j
Rows(j - 2 & ":" & j - 1).Copy Destination:=Range("A" & j)
Range("A" & j & ":O" & j + 1).ClearContents
Cells(j, 1) = "本页小计"
Cells(j + 1, 1) = "总计"
Rows(j & ":" & j + 1).Font.Bold = True
ActiveWindow.View = xlPageBreakPreview
For r = 1 To 2
For i = 1 To ActiveSheet.HPageBreaks.Count
Set hb = ActiveSheet.HPageBreaks(i).Location
If hb.Offset(-2, 0) <> "本页小计" Then
hb.Offset(-2, 0).EntireRow.Insert
hb.Offset(-2, 0).EntireRow.Insert
hb.Offset(-4, 0) = "本页小计"
hb.Offset(-3, 0) = "累计"
Rows(hb.Offset(-4, 0).Row & ":" & hb.Offset(-3, 0).Row).Font.Bold = True
For m = 1 To Len(bt) - 1
b = Mid(bt, m, WorksheetFunction.Find(",", bt, m) - m)
If i = 1 Then sh = "A" & k Else: sh = ActiveSheet.HPageBreaks(i - 1).Location.Address(0, 0)
hb.Offset(-4, b - 1) = "=SUBTOTAL(9," & Range(sh).Offset(0, b - 1).Address(0, 0) & ":" & hb.Offset(-5, b - 1).Address(0, 0) & ")"
hb.Offset(-3, b - 1) = "=SUBTOTAL(9," & Range("A" & k).Offset(0, b - 1).Address(0, 0) & ":" & hb.Offset(-5, b - 1).Address(0, 0) & ")"
m = m + Len(b)
Next m
End If
Next i
Next r
For m = 1 To Len(bt) - 1
b = Mid(bt, m, WorksheetFunction.Find(",", bt, m) - m)
If i = 1 Then sh = "A" & k Else: sh = ActiveSheet.HPageBreaks(i - 1).Location.Address(0, 0)
zc = Range("A65536").End(xlUp).Row
Cells(zc, 1).Offset(-1, b - 1) = "=SUBTOTAL(9," & Range(sh).Offset(0, b - 1).Address(0, 0) & ":" & Cells(zc, 1).Offset(-2, b - 1).Address(0, 0) & ")"
Cells(zc, 1).Offset(0, b - 1) = "=SUBTOTAL(9," & Range("A" & k).Offset(0, b - 1).Address(0, 0) & ":" & Cells(zc, 1).Offset(-2, b - 1).Address(0, 0) & ")"
m = m + Len(b)
Next m
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
End Sub
Sub a删除小计()
Application.ScreenUpdating = False
For i = 4 To Range("A65536").End(xlUp).Row
If Cells(i, 1) = "本页小计" Or Cells(i, 1) = "累计" Or Cells(i, 1) = "总计" Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
Next i
Application.ScreenUpdating = True
End Sub

ae9480

请教老师：这是一个23行自动分页小计累计的代码，假若要改为25行和30行自动分页小计，要改那个值？谢谢！
Sub a增加小计()
Do
bt = Application.InputBox(prompt:="请输入要求和的列，用数字表示。" & Chr(13) & "如D列则输入4，多列用半角“,”分开：", Title:="输入需要求和的列")
If bt = False Then Exit Sub
bt = bt & ","
For k = 1 To Len(bt) - 1
b = Mid(bt, k, WorksheetFunction.Find(",", bt, k) - k)
If b > 1 And b < 257 Then
Else
MsgBox "输入列数格式错误，请重新输入！", vbOKOnly, "错误"
Exit For
End If
k = k + Len(b)
Next k
Loop Until k > Len(bt)
k = 0
Do
k = k + 1
If k > 50 Then MsgBox "未找到起始行。（A列序号为1的那行）", vbOKOnly, "失败": Exit Sub
Loop Until Cells(k, 1) = 1
Application.ScreenUpdating = False
For j = 4 To Range("A65536").End(xlUp).Row
If Cells(j, 1) = "本页小计" Or Cells(j, 1) = "累计" Or Cells(j, 1) = "总计" Then
Rows(j).Delete shift:=xlUp
j = j - 1
End If
Next j
Rows(j - 2 & ":" & j - 1).Copy Destination:=Range("A" & j)
Range("A" & j & ":O" & j + 1).ClearContents
Cells(j, 1) = "本页小计"
Cells(j + 1, 1) = "总计"
Rows(j & ":" & j + 1).Font.Bold = True
ActiveWindow.View = xlPageBreakPreview
For r = 1 To 2
For i = 1 To ActiveSheet.HPageBreaks.Count
Set hb = ActiveSheet.HPageBreaks(i).Location
If hb.Offset(-2, 0) <> "本页小计" Then
hb.Offset(-2, 0).EntireRow.Insert
hb.Offset(-2, 0).EntireRow.Insert
hb.Offset(-4, 0) = "本页小计"
hb.Offset(-3, 0) = "累计"
Rows(hb.Offset(-4, 0).Row & ":" & hb.Offset(-3, 0).Row).Font.Bold = True
For m = 1 To Len(bt) - 1
b = Mid(bt, m, WorksheetFunction.Find(",", bt, m) - m)
If i = 1 Then sh = "A" & k Else: sh = ActiveSheet.HPageBreaks(i - 1).Location.Address(0, 0)
hb.Offset(-4, b - 1) = "=SUBTOTAL(9," & Range(sh).Offset(0, b - 1).Address(0, 0) & ":" & hb.Offset(-5, b - 1).Address(0, 0) & ")"
hb.Offset(-3, b - 1) = "=SUBTOTAL(9," & Range("A" & k).Offset(0, b - 1).Address(0, 0) & ":" & hb.Offset(-5, b - 1).Address(0, 0) & ")"
m = m + Len(b)
Next m
End If
Next i
Next r
For m = 1 To Len(bt) - 1
b = Mid(bt, m, WorksheetFunction.Find(",", bt, m) - m)
If i = 1 Then sh = "A" & k Else: sh = ActiveSheet.HPageBreaks(i - 1).Location.Address(0, 0)
zc = Range("A65536").End(xlUp).Row
Cells(zc, 1).Offset(-1, b - 1) = "=SUBTOTAL(9," & Range(sh).Offset(0, b - 1).Address(0, 0) & ":" & Cells(zc, 1).Offset(-2, b - 1).Address(0, 0) & ")"
Cells(zc, 1).Offset(0, b - 1) = "=SUBTOTAL(9," & Range("A" & k).Offset(0, b - 1).Address(0, 0) & ":" & Cells(zc, 1).Offset(-2, b - 1).Address(0, 0) & ")"
m = m + Len(b)
Next m
ActiveWindow.View = xlNormalView
Application.ScreenUpdating = True
End Sub
Sub a删除小计()
Application.ScreenUpdating = False
For i = 4 To Range("A65536").End(xlUp).Row
If Cells(i, 1) = "本页小计" Or Cells(i, 1) = "累计" Or Cells(i, 1) = "总计" Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
Next i
Application.ScreenUpdating = True
End Sub
自动分页小计累计.rar (21.39 KB, 下载次数: 18)

2012-9-25 20:10 上传

点击文件名下载附件

疾风、骑士

向前辈致敬

abc123qqq

“心”的体会，我亦有同感啊

樱果

这样持续到2007年，开始接手生产计划。库存有了，BOM有了，生产有了，还缺什么呢？预测！那就往销售MM要预测数据吧，根据历史12个月发货记录，把预测分拆成详细的产品（客户固定，产品比例固定）。库存+生产+已销售-预测-安全库存=我要给车间的生产单，简单。生产单放到系统，车间计划员运行拉料单，产生原材料需求。
高手 能把你做的这个表发给我吗？非常感谢！！

longzejian

既然这样。楼主帮忙解答下我的题目吧！http://club.excelhome.net/thread-941006-1-1.html

莫悠悠

毛主席教导我们：在战争中学习战争！我也不是学专科出身，但是就是对VBA产生了浓厚兴趣，也解决了不少问题，我现在的阶段是采购零部件，自建组装车间，这样做的后果是代码繁杂，运算速度慢，没办法，没到哪个级别。
在此想请教版主：输入会计凭证的时候，特别是转账凭证，行数都在50行左右，怎样实现凭证自动分页？1/50，2/50，3/50，4/50……

Sigma-B

小弟是个新手，现在我要设置：1.打印同个文档里2个SHEET的文件；2把这2个SHEET的文件另存为一个新的文档：A.该2个SHEET以前是公式，现在不要公式了，只要值，结构还一致；B新文档的名字用指定单元格命名，位置的捷径也放在个单元格。要怎么设置，谢谢