Sub macro (1)
'
"Acer 1
'
'
'intercept errors
'On Error GoTo Err1
'On the Error Resume Next
Dim I As an Integer, J As an Integer, Row_number As Integer
Dim Total_value As Double
Dim Sum_value As Double
Dim As Double D, E, F As Double As a Double
Dim Conut_sum As Double
As Double Dim F1, F2 As Double, F3 As Double
Dim Sn_number As Integer
Dim Sn As String
Sheet1. Activate
Rows_number=ActiveSheet. UsedRange. Rows. Count
Sn_number=1
For I=2 To Rows_number
If the Range (" F "& amp; I). The Value & lt; Then 100000
Total_value=https://bbs.csdn.net/topics/Total_value + Range (" F "& amp; I). The Value
If Total_value & gt; Then 100000
Total_value=https://bbs.csdn.net/topics/Range (" F "& amp; I). The Value
Sn_number=Sn_number + 1
'SN assignment
Sn=Format (Date, "YYYYMM) & amp; The Format (Sn_number, "000000")
Cells (I, 7)=Sn
'to recalculate the number of rows
Rows_number=ActiveSheet. UsedRange. Rows. Count
'Sn_number=Sn_number + 1
GoTo NextLoop
End the If
The Else
D=Range (" D "& amp; I). The Value 'number
E=Range (" E "& amp; I). The Value 'unit price (in addition to tax)
F=Range (" F "& amp; I). The Value amount of '
Sum_value=https://bbs.csdn.net/topics/0
For D=1 To D
Sum_value * E=https://bbs.csdn.net/topics/D
If Sum_value & gt; Then 100000
'J=I
'I=Selection. Row + 1
'Selection. Insert Shift:=xlUp
Rows (I + 1 & amp; ":" & amp; I + 1). Select
Selection. Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection. Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range (" A "& amp; (I + 1)). Value=https://bbs.csdn.net/topics/Range (" A "& amp; I). The Value 'name
Range (" B "& amp; (I + 1)). Value=https://bbs.csdn.net/topics/Range (" B "& amp; I). The Value 'type
Range (" C "& amp; (I + 1)). Value=https://bbs.csdn.net/topics/Range (" C "& amp; Value units'
I).Range (" D "& amp; (I + 1)). 'number Value=https://bbs.csdn.net/topics/D - 1
Range (" E "& amp; (I + 1)). Value=https://bbs.csdn.net/topics/E 'unit price (with the exception of tax)
Range (" F "& amp; (I + 1)). Value=https://bbs.csdn.net/topics/(D - 1) * E amount
'
Range (" A "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/Range (" A "& amp; I). The Value 'name
Range (" B "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/Range (" B "& amp; I). The Value 'type
Range (" C "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/Range (" C "& amp; Value units'
I).Range (" D "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/Range (" D "& amp; I). The Value - Range (" D "& amp; (I + 1)). The Value number
'Range (" E "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/E 'unit price (with the exception of tax)
Range (" F "& amp; (I + 2)). Value=https://bbs.csdn.net/topics/(Range (" D "& amp; (I + 2)). The Value), amount of * E '
'Exit Sub
Conut_sum=0
'check the number and amount of additive added is equal to the original data
If the Range (" D "& amp; (I + 1)). The Value + Range (" D "& amp; (I + 2)). The Value & lt;> Range (" D "& amp; I). The Value Then
MsgBox "the first" & amp; I & amp; "Number is not equal," vbExclamation
The Exit Sub
The Else
If Round (Cells (I + 1, 6) + Cells (I + 2, 6), 2) & lt;> Round (Cells (I, 6), 2) Then
MsgBox "the first" & amp; I & amp; Is not equal to "line, please check", vbExclamation
The Exit Sub
End the If
End the If
Rows (I). The Delete
Total_value=https://bbs.csdn.net/topics/0
Sn_number=Sn_number + 1
'SN assignment
Sn=Format (Date, "YYYYMM) & amp; The Format (Sn_number, "000000")
Cells (I, 7)=Sn
'to recalculate the number of rows
Rows_number=ActiveSheet. UsedRange. Rows. Count
Sn_number=Sn_number + 1
GoTo NextLoop
End the If
Next D
End the If
'SN assignment
Sn=Format (Date, "YYYYMM) & amp; The Format (Sn_number, "000000")
Cells (I, 7)=Sn
'to recalculate the number of rows
Rows_number=ActiveSheet. UsedRange. Rows. Count
NextLoop:
Next I
'error handling
Err1:
MsgBox "wrong!" & VbCrLf & amp; "Wrong number," & amp; Err. Number & amp; Error description: "" & amp; Err. The Description
The Exit Sub
End Sub
Table of field format for
As long as the execution error will be reported to the zero error, if error trapping close also is not the same thing, I suspect that is a question of the last cycle, if the line number is 6, when the line 6 is more than 10 w, will split two data, and then to recalculate the number of rows of 7, so when this will be an error, because before calculating the number of rows is 6, can only be run to the line 6, but I am the end of the cycle number is 7, don't know why
The role of the macro is detailed combined total amount & lt; 100000 is a SN, if itself is a data & gt; Number is 100000, that is about to break up the amount, to be opened, after opening the total amount will & lt; 100000.
CodePudding user response:
0 is the normal meaning, filter out the codeCodePudding user response:
If Err. Number<> 0 then MsgBox "wrong!" & VbCrLf & amp; "Wrong number," & amp; Err. Number & amp; Error description: "" & amp; Err. The DescriptionCodePudding user response: