[Original] batch processing of vehicle environmental protection inspection mark Issuing System

Keywords: VBA, Excel, Macro, button genie, Environmental Protection icon

Sub Macro1 ()
'The raw data of the vehicle environmental inspection mark issuing system is sorted by VBA
'Macro1 Macro
'Macro recorded by MS User, time:
'

'Vehicle type
'Sheet2.activate
'Dim I As Integer
'Dim m As Integer, n As Integer
'M = 12
'N' = 13
'For I = 2 To 10000
'If Cells (I, m). Value = "K31" Then
'Cells (I, n). Value = "small Common Passenger Car"
'Elseif Cells (I, m). Value = "K32" Then
'Cells (I, n). Value = "Small Cross-Country Bus"
'Elseif Cells (I, m). Value = "K33" Then
'Cells (I, n). Value = "car"
'Elseif Cells (I, m). Value = "K34" Then
'Cells (I, n). Value = "small dedicated passenger car"
'Elseif Cells (I, m). Value = "K21" Then
'Cells (I, n). Value = "medium-sized coach"
'Elseif Cells (I, m). Value = "K11" Then
'Cells (I, n). Value = "large common passenger car"
'Elseif Cells (I, m). Value = "H31" Then
'Cells (I, n). Value = "Light Truck"
'Elseif Cells (I, m). Value = "H32" Then
'Cells (I, n). Value = "light van"
'Elseif Cells (I, m). Value = "H33" Then
'Cells (I, n). Value = "Light Duty closed truck"
'Elseif Cells (I, m). Value = "N11" Then
'Cells (I, n). Value = "tricycle"
'Elseif Cells (I, m). Value = "Z51" Then
'Cells (I, n). Value = "Heavy Duty special vehicle"
'Elseif Cells (I, m). Value = "K43" Then
'Cells (I, n). Value = "Mini Car"
'Elseif Cells (I, m). Value = "H37" Then
'Cells (I, n). Value = "light self-unloading truck"
'Else
'Cells (I, n). Interior. ColorIndex = 3
'End If
'Next I

''Usage
'Sheet2.activate
'Dim I As Integer
'For I = 2 To 10000
'If Cells (I, 10). Value = "A" Then
'Cells (I, 11). Value = "non-operational"
'Elseif Cells (I, 10). Value = "C" Then
'Cells (I, 11). Value = "bus"
'Elseif Cells (I, 10). Value = "D" Then
'Cells (I, 11). Value = "rent"
'Else
'Cells (I, 11). Value = "others"
'Cells (I, 11). Interior. ColorIndex = 3
'End If
'Next I

''Fuel
'Sheet2.activate
'Dim I As Integer
'
'For I = 2 To 10000
'If Cells (I, 6). Value = "A" Then
'Cells (I, 7). Value = "Gasoline"
'Elseif Cells (I, 6). Value = "B" Then
'Cells (I, 7). Value = "diesel"
'Else
'Cells (I, 7). Value = "unknown"
'Cells (I, 7). Interior. ColorIndex = 3
'End If
'Next I

''License plate
'Sheet2.activate
'Dim I As Integer
'
'For I = 2 To 10000
'If Cells (I, 2). Value = "02" Then
'Cells (I, 3). Value = "Blue Card"
'Elseif Cells (I, 2). Value = "01" Then
'Cells (I, 3). Value = "Yellow Card"
'Elseif Cells (I, 2). Value = "13" Then
'Cells (I, 3). Value = "Blue Card"
''16 is a coach. 15 is half-mounted.
'Elseif Cells (I, 2). Value = "16" Or Cells (I, 2). Value = "15" Then
'Cells (I, 3). Value = "Yellow Card"
'Else
'Debug. Print I
'Cells (I, 3). Interior. ColorIndex = 3
'End If
'Next I

''Number of passengers
'Sheet2.activate
'Dim I As Integer
'
'For I = 2 To 10000
'If Cells (I, 4). Value = "" Then
'Cells (I, 5). Value = 2
'Cells (I, 5). Interior. ColorIndex = 3
'Else
'Cells (I, 5). Value = Cells (I, 4). Value
'End If
'Next I

'Total Quality
Sheet2.Activate
Dim I As Integer
Dim m As Integer
M = 14

For I = 2 To 10000
If Cells (I, m). Value = "" Then
Debug. Print (I-1)
Cells (I, m). Interior. ColorIndex = 3
End If
Next I
End Sub 'check whether the import result is consistent with the export result:
Sub Macro1 ()
'Macro recorded by MS User, time:
'Shortcut: Ctrl + k
Sheet2.Activate
Dim I As Integer
Dim j As Integer
Dim m As Integer
Dim n As Integer
Dim B As Integer
M = 1
N = 2

Debug. Print "Begin"
For j = 2 To 2001 '100%
B = 0
For I = 2 To 2006 '1995
If Sheet1.Cells (I, m). Value = Sheet2.Cells (j, n). Value Then
B = 1
Exit
End If
Next I
If B = 0 Then Debug. Print j
If j Mod 100 = 0 Then Debug. print "=" & j & "========================="
Next j
Debug. Print "End"
End Sub 'keypair'
'Copy the license plate
'Use environment: it must be in XP and there must be a Windows Folder window before the software is released.

'========== Copy the license plate begin ============
VPC: MoveTo 329,678
LeftDown 1
LeftClick 1
LeftUp 1
LeftUp 1
LeftUp 1
LeftUp 1
LeftUp 1
Delay250
KeyDown "Ctrl", 1
Delay 16
KeyDown "Ctrl", 1
Delay375
KeyDown "C", 1
Delay156
KeyUp "Ctrl", 1
KeyUp "C", 1
Delay 16
KeyUp "C", 1
VPC: MoveTo 627,759
'Delay 937
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 140,329
Delay200
LeftDown 1
VPC: MoveTo 57,329
Delay422
LeftUp 1
VPC: MoveTo 59,329
Delay 94
KeyDown "Ctrl", 1
VPC: MoveTo 118,329
Delay203
KeyDown "V", 1
VPC: MoveTo 130,329
Delay 94
KeyUp "Ctrl", 1
VPC: MoveTo 139,329
Delay 47
KeyUp "V", 1
VPC: MoveTo 151,332
KeyDown "Enter", 1

'Double-click record
VPC: MoveTo 144,377
Delay 50
'Leftdown 1
LeftDoubleClick 1
'Leftup 1

'============ Copy the license plate end ==========' click the wizard
'Function: Release confirmation, shortcut: F6
'Use environment: it must be in XP and there must be a Windows Folder window before the software is released.

// ============ The following is the content recorded by the press Key genie ============
'The main program is as follows:
Delay100
VPC: MoveTo 596,545
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 440,498
Delay2800
LeftDown 1
LeftClick 1
LeftUp 1
Delay 16
LeftUp 1
VPC: MoveTo 931,580
Delay210
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 768,438
Delay210
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 775,438
Delay210
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 653,439
Delay210
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 792,435
Delay2300
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 644,436
Delay250
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 734,767
Delay900
LeftDown 1
LeftClick 1
LeftUp 1
MoveTo 608, 90
'Delay 100
LeftDown 1
LeftClick 1
LeftUp 1
'Delay 1000
KeyDown "Down", 1
'Delay 47
KeyUp "Down", 1
'============ The above is the content recorded by the press Key genie ==========
'========== Copy the license plate begin ============
'Copy the license plate
VPC: MoveTo 329,678
LeftDown 1
LeftClick 1
LeftUp 1
LeftUp 1
LeftUp 1
LeftUp 1
LeftUp 1
Delay250
KeyDown "Ctrl", 1
Delay 16
KeyDown "Ctrl", 1
Delay375
KeyDown "C", 1
Delay156
KeyUp "Ctrl", 1
KeyUp "C", 1
Delay 16
KeyUp "C", 1
VPC: MoveTo 627,759
LeftDown 1
LeftClick 1
LeftUp 1
VPC: MoveTo 140,329
Delay200
LeftDown 1
VPC: MoveTo 57,329
Delay422
LeftUp 1
VPC: MoveTo 59,329
Delay 94
KeyDown "Ctrl", 1
VPC: MoveTo 118,329
Delay200
KeyDown "V", 1
VPC: MoveTo 130,329
Delay 90
KeyUp "Ctrl", 1
VPC: MoveTo 139,329
Delay 40
KeyUp "V", 1
VPC: MoveTo 151,332
KeyDown "Enter", 1

'Double-click record
VPC: MoveTo 144,377
Delay 50
LeftDoubleClick 1
'============ Copy the license plate end ==========' click the wizard
'Function: Date input
'Use environment: it must be in XP and there must be a Windows Folder window before the software is released.
'========== The following is the content recorded by the press Key genie ==========
VPC: MoveTo 362,473
LeftDown 1
LeftClick 1
LeftUp 1

KeyDown "Num 2", 1

KeyDown "Num 0", 1

KeyUp "Num 2", 1

KeyUp "Num 0", 1

KeyDown "Num 1", 1

KeyUp "Num 1", 1

KeyDown "Num 3", 1

KeyUp "Num 3", 1

KeyDown "Tab", 1

KeyUp "Tab", 1

KeyDown "Num 1", 1

KeyUp "Num 1", 1

KeyDown "Num 2", 1

KeyUp "Num 2", 1

KeyDown "Tab", 1

KeyUp "Tab", 1

KeyDown "Num 3", 1

KeyDown "Num 1", 1

KeyUp "Num 3", 1

KeyUp "Num 1", 1
'============ The above is the content recorded by the press Key genie ==========
'============ By F6 ==============
Delay100
KeyPress "F6", 1