|
本帖最后由 feiaoli 于 2021-1-23 15:12 编辑
需要修改:县级预算单位项目资金支出计划申请书中的宏
求助VBA:
****把县级预算单位项目资金支出计划申请书模块中的"保存"宏更换成"领用保存"宏
参考宏模块为"道具领用登记"压缩包中的 "领用保存" 宏
烦劳各位师傅出手,不胜感激!
请修改以下宏
- Sub 领用保存()
- Dim r&, i&, arr, brr, d As Object
- With Worksheets("出库汇总表")
- r = .Range("h" & Rows.Count).End(xlUp).Row
- If r > 1 Then
- arr = .Range("h2:h" & r)
- Set d = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(arr): d(arr(i, 1)) = "": Next
- End If
- End With
- r = Range("b" & Rows.Count).End(xlUp).Row
- If r < 5 Then MsgBox "无数据需保存": Exit Sub
-
-
- arr = Range("b5:g" & r)
- For i = 1 To UBound(arr): arr(i, 6) = Range("d3"): Next
- ReDim brr(1 To UBound(arr), 1 To 2)
- For i = 1 To UBound(brr)
- brr(i, 1) = Range("f3")
- brr(i, 2) = Range("c3")
- Next
-
- If Not d Is Nothing Then
- If Not d.exists(Range("d3").Value) Then
- With Worksheets("出库汇总表").Range("a" & Rows.Count).End(xlUp)
- .Offset(1).Resize(UBound(brr), 2) = brr
- .Offset(1, 2).Resize(UBound(arr), 6) = arr
- End With
- End If
- Else
- With Worksheets("出库汇总表").Range("a" & Rows.Count).End(xlUp)
- .Offset(1).Resize(UBound(brr), 2) = brr
- .Offset(1, 2).Resize(UBound(arr), 6) = arr
- End With
- End If
- End Sub
复制代码
替换以下宏
- Sub 保存()
- Sheets("县级预算单位项目资金支出计划申请书").Select
- lastrow = Sheets("县级预算单位项目资金支出计划数据库").Cells(Rows.Count, "d").End(3).Row + 1
- With Sheets("县级预算单位项目资金支出计划数据库")
- .Cells(lastrow, 2) = [B2].Value
- .Cells(lastrow, 3) = [E2].Value
- .Cells(lastrow, 4) = [B3].Value
- .Cells(lastrow, 5) = [D3].Value
- .Cells(lastrow, 6) = [F3].Value
- .Cells(lastrow, 7) = [B4].Value
- .Cells(lastrow, 8) = [D4].Value
- .Cells(lastrow, 9) = [F4].Value
- For n = 1 To 8
- .Cells(lastrow, n + 9) = Cells(n + 10, "m")
- Next n
- End With
- End Sub
复制代码
|
|