Sub scpz()
Dim i As Integer, j As Integer
Dim rq
Dim rowNo As Long, rng As Range
Set rng = Sheet17.Range("D2:D4000").Find("", , , xlValue)
If Not rng Is Nothing Then
rowNo = rng.Row '行号
End If
Dim star As Long, rag As Range
Set rag = Sheet10.Range("O8:O4000").Find("", , , xlValue)
If Not rag Is Nothing Then
star = rag.Row '非空行号
End IF
Dim num
With Worksheets("资金日记账")
num = ActiveSheet.Range("e" & Rows.Count).End(xlUp).Row
End With
showProgressBar '调用进度条
i = star '日记账行数
j = rowNo '账务处理行数
rq = Now()
For i = star To num
If Sheet10.Cells(i, 4) <> "" And Sheet10.Cells(i, 15) <> "√" And Sheet10.Cells(i, 4) >= Sheet10.Cells(6, 10) And Sheet10.Cells(i, 4) <= Sheet10.Cells(6, 12) Then
Sheet17.Cells(j, 6) = Sheet10.Cells(i, 4)
Sheet17.Cells(j, 7) = Sheet10.Cells(i, 6)
Sheet17.Cells(j, 8) = Sheet10.Cells(i, 16)
Sheet17.Cells(j, 9) = Sheet10.Cells(i, 17)
If Sheet10.Cells(i, 8) = "" Then
Sheet17.Cells(j, 10) = Sheet10.Cells(i, 9)
Else
Sheet17.Cells(j, 10) = Sheet10.Cells(i, 8)
End If
Sheet17.Cells(j, 11) = rq
Sheet17.Cells(j, 12) = "日记账生成"
Sheet17.Cells(j, 13) = Sheet10.Cells(i, 7)
j = j + 1
Sheet17.Cells(j, 6) = Sheet10.Cells(i + 1, 4)
Sheet17.Cells(j, 7) = Sheet10.Cells(i + 1, 6)
Sheet17.Cells(j, 8) = Sheet10.Cells(i + 1, 16)
Sheet17.Cells(j, 9) = Sheet10.Cells(i + 1, 17)
If Sheet10.Cells(i + 1, 8) = "" Then
Sheet17.Cells(j, 10) = Sheet10.Cells(i + 1, 9)
Else
Sheet17.Cells(j, 10) = Sheet10.Cells(i + 1, 8)
End If
Sheet17.Cells(j, 11) = rq
Sheet17.Cells(j, 12) = "日记账生成"
Sheet17.Cells(j, 13) = Sheet10.Cells(i + 1, 7)
Sheet10.Cells(i, 15) = "√" '反写日记账生成状态
End If
Next
ActiveWorkbook.Unprotect Password:="12315"
Sheets("账务处理").Visible = True
Application.DisplayAlerts = True
Sheets("账务处理").Activate
qinchu
MsgBox "本期资金日记账已生成凭证!", vbExclamation, "凭证生成提示信息"
ActiveWorkbook.Protect Password:="12315", Structure:=True, Windows:=False
Application.DisplayAlerts = True
If Sheet10.Cells(i, 6) <> "" And Sheet10.Cells(i, 15) = "√" Then
'Put in **Sheet 8** what is in **Sheet 9**
MsgBox "本期日记账已生成凭证,无需重复生成!", vbExclamation, "凭证生成提示信息"
End If
End Sub
代码优化建议:
变量命名:变量名应该具有描述性,让人能够理解变量的含义。例如,使用更具体的变量名来代替 i、j、num 等。
减少重复计算:在代码中多次使用相同的计算,可以将计算结果存储在一个变量中,以减少计算次数。
使用 With 语句:使用 With 语句可以减少代码中的嵌套,提高代码可读性。
避免使用 Select 和 Activate:在代码中避免使用 Select 和 Activate 语句,以减少 Excel 的无响应和卡顿。
避免使用 Find 函数:在代码中使用 Find 函数来查找单元格,会增加代码的复杂性和运行时间。建议直接使用循环来遍历单元格。
使用数组:对于大量数据的操作,可以使用数组来存储数据,以减少对 Excel 的操作次数,提高运行速度。
避免使用 If...Else 语句:在代码中使用 If...Else 语句会增加代码的复杂性和运行时间。如果条件简单,可以直接使用单一语句代替。
根据以上建议,可以对代码进行优化:
Sub scpz()
Dim startRow As Long, endRow As Long Dim nonEmptyRow As Long, j As Long Dim data() As Variant Dim i As Long, k As Long Dim currentDate As Date Dim message As String
startRow = 8 '账务处理起始行号 endRow = 4000 '账务处理结束行号 nonEmptyRow = 0 '日记账非空行号 j = 0 '当前行号 k = 0 '计数器 currentDate = Now() '获取当前日期 message = "本期资金日记账已生成凭证!" '生成提示信息
'获取资金日记账最后一行号 With Worksheets("资金日记账") num = .Cells(Rows.Count, "E").End(xlUp).Row End With
'将数据存储到数组中 data = Sheet10.Range("O8:O" & num).Value
'调用进度条 Call showProgressBar
'遍历数据并生成日记账 For i = startRow To num If data(i, 4) <> "" And data(i, 15) <> "√" And data(i, 4) >= Sheet10.Cells(6, 10) And data(i, 4) <= Sheet10.Cells(6, 12) Then nonEmptyRow = i '记录非空行号 j = j + 1 '增加当前行号 Sheet17.Cells(j, 6) = data(i, 4) '复制数据到日记账中 Sheet17.Cells(j, 7) = data(i, 6) Sheet17.Cells(j, 8) = data(i, 16) Sheet17.Cells(j, 9) = data(i, 17) If data(i, 8) = "" Then Sheet17.Cells(j, 10) = data(i, 9) '如果备注为空,则使用摘要作为备注 Else Sheet17.Cells(j, 10) = data(i, 8) '否则使用实际备注 End If Sheet17.Cells(j, 11) = currentDate '记录当前日期 Sheet17.Cells(j, 12) = "日记账生成" '记录操作类型为日记账生成 Sheet17.Cells(j, 13) = data(i, 7) '复制凭证信息到日记账中 k = k + 1 '增加计数器,表示生成了一条日记账记录 ElseIf data(i + 1, 4) <> "" Then '如果下一行不为空,则生成下一行日记账记录 nonEmptyRow = i + 1 '记录非空行号 j = j + 1 '增加当前行号 Sheet17.Cells(j, 6) = data(i + 1, 4) '复制数据到日记账中 Sheet17.Cells(j, 7) = data(i + 1, 6) Sheet17.Cells(j, 8) = data(i + 1, 16) Sheet17.Cells(j, 9) = data(i + 1, 17) If data(i + 1, 8) = "" Then Sheet17.Cells(j, 10) = data(i + 1