将下面格式的菜单:
处理成下面这样的:(三列)GRX12# -1009 w/ Smart Key REIZ Powertrain 0 Engine 1 ECT 1 Cruise Control 1 Chassis 0 ABS/VSC/TRC 1 EMPS 1 Body 0 Air Conditioner 1 SRS Airbag 1 Body 1 Gateway 1 Body No.5 1 Master Switch 1 Sliding Roof 1 Combination Meter 1 AFS 1 Entry&Start 1 Power Source Control 1 w/o Smart Key REIZ Powertrain 0 Engine 1 ECT 1 Cruise Control 1 Chassis 0 ABS/VSC/TRC 1 EMPS 1 Body 0 Air Conditioner 1 Immobiliser 1 SRS Airbag 1 Body 1 Gateway 1 Body No.5 1 Master Switch 1 Sliding Roof 1 Combination Meter 1 AFS 1 1009- w/ Smart Key w/ AFS OFF Switch REIZ Powertrain 0 Engine and ECT 1 Radar Cruise 1 Chassis 0 Air suspension 1 ABS/VSC/TRC 1 EMPS 1 VGRS 1 IPA/Parking Assist Monitor 1 Body Electrical 0 Air Conditioner 1 SRS Airbag 1 Pre-Crash 2 1 Main Body 1 PM1 Gateway 1 PM2 Gateway 1 D-Door Motor 1 P-Door Motor 1 RL-Door Motor 1 RR-Door Motor 1 Master Switch 1 Sliding Roof 1 Tilt&Telescopic 1 Combination Meter 1 AFS 1 Entry&Start 1 Power Source Control 1 Starting Control 1 Navigation System 1
GRX12# 1 REIZ 2 w/ Smart Key 3 Powertrain 4 Engine 5 0x07E0 ECT 5 0x07E1 Cruise Control 5 0x17E0 Chassis 4 ABS/VSC/TRC 5 0x5029 EMPS 5 0x5032 Body 4 Air Conditioner 5 0x9098 SRS Airbag 5 0x9058 Body 5 0x9040 Gateway 5 0x9840 Body No.5 5 0x9051 Master Switch 5 0x90EC Sliding Roof 5 0x90AD Combination Meter 5 0x9062 AFS 5 0x9070 Entry&Start 5 0x90B5 Power Source Control 5 0x90E9 w/o Smart Key 3 Powertrain 4 Engine 5 0x07E0 ECT 5 0x07E1 Cruise Control 5 0x17E0 Chassis 4 ABS/VSC/TRC 5 0x5029 EMPS 5 0x5032 Body 4 Air Conditioner 5 0x9098 Immobiliser 5 0xD0C1 SRS Airbag 5 0x9058 Body 5 0x9040 Gateway 5 0x9840 Body No.5 5 0x9051 Master Switch 5 0x90EC Sliding Roof 5 0x90AD Combination Meter 5 0x9062 AFS 5 0x9070 MARK X 2 w/ Smart Key 3 Powertrain 4 Engine 5 0x07E0 ECT 5 0x07E1 Cruise Control 5 0x17E0 Chassis 4 ABS/VSC/TRC 5 0x5029 EMPS 5 0x5032 Body 4 Air Conditioner 5 0x9098 SRS Airbag 5 0x9058 Body 5 0x9040 Gateway 5 0x9840 Body No.5 5 0x9051 Master Switch 5 0x90EC Sliding Roof 5 0x90AD Combination Meter 5 0x9062 AFS 5 0x9070 Entry&Start 5 0x90B5 Power Source Control 5 0x90E9 w/o Smart Key 3 Powertrain 4 Engine 5 0x07E0 ECT 5 0x07E1 Cruise Control 5 0x17E0 Chassis 4 ABS/VSC/TRC 5 0x5029 EMPS 5 0x5032 Body 4 Air Conditioner 5 0x9098 Immobiliser 5 0xD0C1 SRS Airbag 5 0x9058 Body 5 0x9040 Gateway 5 0x9840 Body No.5 5 0x9051 Master Switch 5 0x90EC Sliding Roof 5 0x90AD Combination Meter 5 0x9062 AFS 5 0x9070 VBA脚本如下:
Private Sub CreateMenu_Click() Dim strModel As String Dim strMenu1 As String Dim strMenu2 As String Dim strMenu3 As String Dim strSystem As String Dim strProtocol As String Dim Level As Integer Dim Line As Long Dim i As Integer Dim j As Integer Dim k As Integer Dim i1 As Integer Dim i2 As Integer Dim k1 As Integer Dim k2 As Integer Dim num As Long Dim cell As Range Dim SystemNum As Long Dim m1 As Long Dim m2 As Long Dim m3 As Long Dim m4 As Long Dim m As Long Dim line3 As Long Dim menuLevel As Integer Dim flag As Integer Set cell = Sheet2.Range("A65536").End(xlUp) num = cell.Row Set cell = Nothing Set cell = Sheet2.Range("H65536").End(xlUp) SystemNum = cell.Row Set cell = Nothing Line = 1 line3 = 1 flag = 0 menuLevel = 1 Do While (1) strModel = Trim(Sheet2.Cells(Line, 1)) j = Line Do While (1) j = j + 1 If (Trim(Sheet2.Cells(j, 1)) <> "") Then Exit Do End If If (j > num) Then Exit Do End If Loop If (j > num) Then Exit Do End If If (j - Line < 5) Then Else i = Line + 1 If (flag = 0) Then flag = 1 Sheet3.Cells(line3, 1) = strModel Sheet3.Cells(line3, 2) = menuLevel line3 = line3 + 1 End If 'menu1 If (Trim(Sheet2.Cells(i, 2)) <> "") Then Do While (1) k = i Do While (1) k = k + 1 If (Trim(Sheet2.Cells(k, 2)) <> "") Then Exit Do End If If (k > j) Then k = j Exit Do End If Loop strMenu1 = Trim(Sheet2.Cells(i, 2)) 'menu2 i1 = i + 1 If (Trim(Sheet2.Cells(i1, 3)) <> "") Then Do While (1) k1 = i1 Do While (1) k1 = k1 + 1 If (Trim(Sheet2.Cells(k1, 3)) <> "") Then Exit Do End If If (k1 > k) Then k1 = k Exit Do End If Loop strMenu2 = Trim(Sheet2.Cells(i1, 3)) 'menu3 i2 = i1 + 1 If (Trim(Sheet2.Cells(i2, 4)) <> "") Then Do While (1) k2 = i2 Do While (1) k2 = k2 + 1 If (Trim(Sheet2.Cells(k2, 4)) <> "") Then Exit Do End If If (k2 > k1) Then k2 = k1 Exit Do End If Loop strMenu3 = Trim(Sheet2.Cells(i2, 4)) m4 = i2 Sheet3.Cells(line3, 1) = Sheet2.Cells(m4, 7) Sheet3.Cells(line3, 2) = menuLevel + 1 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu1 Sheet3.Cells(line3, 2) = menuLevel + 2 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu2 Sheet3.Cells(line3, 2) = menuLevel + 3 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu3 Sheet3.Cells(line3, 2) = menuLevel + 4 line3 = line3 + 1 Do While (1) Level = Sheet2.Cells(m4, 9) If (Level = 0) Then Sheet3.Cells(line3, 1) = Sheet2.Cells(m4, 8) Sheet3.Cells(line3, 2) = menuLevel + 5 Else strSystem = Sheet2.Cells(m4, 8) Sheet3.Cells(line3, 1) = strSystem Sheet3.Cells(line3, 2) = menuLevel + 6 End If line3 = line3 + 1 m4 = m4 + 1 If (m4 < k2) Then Else Exit Do End If Loop If (k2 = k1) Then Exit Do End If i2 = k2 Loop Else m3 = i1 Sheet3.Cells(line3, 1) = Sheet2.Cells(m3, 7) Sheet3.Cells(line3, 2) = menuLevel + 1 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu1 Sheet3.Cells(line3, 2) = menuLevel + 2 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu2 Sheet3.Cells(line3, 2) = menuLevel + 3 line3 = line3 + 1 Do While (1) Level = Sheet2.Cells(m3, 9) If (Level = 0) Then Sheet3.Cells(line3, 1) = Sheet2.Cells(m3, 8) Sheet3.Cells(line3, 2) = menuLevel + 4 Else strSystem = Sheet2.Cells(m3, 8) Sheet3.Cells(line3, 1) = strSystem Sheet3.Cells(line3, 2) = menuLevel + 5 End If line3 = line3 + 1 m3 = m3 + 1 If (m3 < k1) Then Else Exit Do End If Loop End If If (k1 = k) Then Exit Do End If i1 = k1 Loop Else m2 = i Sheet3.Cells(line3, 1) = Sheet2.Cells(m2, 7) Sheet3.Cells(line3, 2) = menuLevel + 1 line3 = line3 + 1 Sheet3.Cells(line3, 1) = strMenu1 Sheet3.Cells(line3, 2) = menuLevel + 2 line3 = line3 + 1 Do While (1) Level = Sheet2.Cells(m2, 9) If (Level = 0) Then Sheet3.Cells(line3, 1) = Sheet2.Cells(m2, 8) Sheet3.Cells(line3, 2) = menuLevel + 3 Else strSystem = Sheet2.Cells(m2, 8) Sheet3.Cells(line3, 1) = strSystem Sheet3.Cells(line3, 2) = menuLevel + 4 End If line3 = line3 + 1 m2 = m2 + 1 If (m2 < k) Then Else Exit Do End If Loop End If 'menu2 If (k = j) Then Exit Do End If i = k Loop Else 'menu1 m1 = Line Sheet3.Cells(line3, 1) = Sheet2.Cells(m1, 7) Sheet3.Cells(line3, 2) = menuLevel + 1 line3 = line3 + 1 Do While (1) Level = Sheet2.Cells(m1, 9) If (Level = 0) Then Sheet3.Cells(line3, 1) = Sheet2.Cells(m1, 8) Sheet3.Cells(line3, 2) = menuLevel + 2 Else strSystem = Sheet2.Cells(m1, 8) Sheet3.Cells(line3, 1) = strSystem Sheet3.Cells(line3, 2) = menuLevel + 3 End If line3 = line3 + 1 m1 = m1 + 1 If (m1 < j) Then Else Exit Do End If Loop End If 'menu1 End If flag = 0 Line = j Loop End Sub
VBA对EXCEL的操作
最新推荐文章于 2024-09-16 09:47:29 发布