|
Sub test()
arr = Sheet1.Range("b154:w184") '数据区域'
ReDim brr(1 To UBound(arr) * 100, 1 To 5) 'UBound函数返回数组的上届数字 避免下标越界的错误
j1 = j + 1
Sheet2.[b2:g65536] = ""
For jj = 1 To UBound(arr, 2) + 1 Step 2
j = Sheet1.Cells(8, 3 + jj)
If jj >= 21 Then
GoTo line2
End If
For i = 1 To UBound(arr)
If arr(i, 1) <> "" And arr(i, jj + 2) <> "" Then
x = x + 1
brr(x, 2) = arr(i, 1)
y = i
a = Split(arr(i, jj + 2), " ")
brr(x, 1) = a(0): brr(x, 3) = a(1)
brr(x, 5) = arr(i, jj + 3)
brr(x, 4) = j
ElseIf arr(i, 1) = "" And arr(i, jj + 2) = "" Then
GoTo line1
ElseIf arr(i, 1) = "" And arr(i, jj + 2) <> "" Then
a = Split(arr(i, jj + 2), " ")
x = x + 1
brr(x, 1) = a(0): brr(x, 3) = a(1)
brr(x, 5) = arr(i, jj + 3)
brr(x, 2) = arr(i, 1)
brr(x, 4) = j
End If
If arr(i, 1) <> brr(x, 2) And arr(i, 1) <> "" Then
y = i
GoTo line2
End If
line1:
Next
line2:
m = Sheet2.[c65536].End(xlUp).Row
Sheet2.Range("c" & m + 1).Resize(UBound(arr), 5) = brr
m1 = Sheet2.[c65536].End(xlUp).Row
line100:
For l = 1 + l1 To m1 - m - 1 + l1
ss = Sheet2.Cells(m + 1 + l1, 4)
If Sheet2.Cells(m + l + 1, 4) = "" Then
ii = Sheet2.Cells(m + l + 1, 4)
Sheet2.Cells(l + m + 1, 4) = ss
Else
l1 = l + m
m = 0
GoTo line100
End If
If l = m1 - 1 Then
GoTo line1000
End If
Next
line1000:
ReDim brr(1 To UBound(arr) * 100, 1 To 5)
x = 0
Next
Call test2
End Sub
Sub test2()
j = Sheet2.[d65536].End(xlUp).Row
arr = Range("d1:d" & j)
For i = 1 To UBound(arr)
If arr(i, 1) = "" Then
m = i - 2
GoTo line1
End If
Next
line1:
m1 = Sheet2.[c65536].End(xlUp).Row
line100:
For l = 1 + l1 To m1 - m - 1 + l1
ss = Sheet2.Cells(m + 1 + l1, 4)
If Sheet2.Cells(m + l + 1, 4) = "" Then
ii = Sheet2.Cells(m + l + 1, 4)
Sheet2.Cells(l + m + 1, 4) = ss
Else
l1 = l + m
m = 0
GoTo line100
End If
If l = m1 - 1 Then
GoTo line1000
End If
Next
line1000:
Call test3
End Sub
Sub test3()
j = Sheet2.[c65536].End(xlUp).Row
ReDim arr(1 To j, 1 To 1)
For i = 1 To j
arr(i, 1) = "1F"
Next
Sheet2.Range("b2").Resize(j - 1, 1) = arr
End Sub
|
|