|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
代码如下。。。。
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$H$1" Then
s = ""
arr = [e51:e335]
crr = arr
If crr(UBound(crr), 1) = Empty Then crr(UBound(crr), 1) = 0
ReDim brr(1 To UBound(arr), 0)
For i = 1 To UBound(arr)
If arr(i, 1) > 0 Then
arr(i, 1) = 1
Else
arr(i, 1) = 0
End If
s = s & CStr(arr(i, 1))
brr(i, 0) = 0
Next
For i = 1 To Len(s)
x = InStr(i, s, "000")
If IsNumeric(x) And x <> 0 Then
brr(x + 2, 0) = 100
i = x
Else
Exit For
End If
Next
For i = 2 To UBound(crr)
If crr(i, 1) = 0 Then
If crr(i - 1, 1) <> 0 Then
For j = i - 1 To 2 Step -1
If crr(j, 1) = 0 Then
If crr(j - 1, 1) <> 0 And crr(j - 1, 1) = crr(i - 1, 1) Then
brr(i, 0) = crr(i - 1, 1)
Exit For
Else
Exit For
End If
End If
Next
End If
End If
Next
[h51].Resize(UBound(brr)) = brr
s = ""
arr = [k51:k335]
crr = arr
If crr(UBound(crr), 1) = Empty Then crr(UBound(crr), 1) = 0
ReDim brr(1 To UBound(arr), 0)
For i = 1 To UBound(arr)
If arr(i, 1) > 0 Then
arr(i, 1) = 1
Else
arr(i, 1) = 0
End If
s = s & CStr(arr(i, 1))
brr(i, 0) = 0
Next
For i = 1 To Len(s)
x = InStr(i, s, "000")
If IsNumeric(x) And x <> 0 Then
brr(x + 2, 0) = 100
i = x
Else
Exit For
End If
Next
For i = 2 To UBound(crr)
If crr(i, 1) = 0 Then
If crr(i - 1, 1) <> 0 Then
For j = i - 1 To 2 Step -1
If crr(j, 1) = 0 Then
If crr(j - 1, 1) <> 0 And crr(j - 1, 1) = crr(i - 1, 1) Then
brr(i, 0) = crr(i - 1, 1)
Exit For
Else
Exit For
End If
End If
Next
End If
End If
Next
[p51].Resize(UBound(brr)) = brr
Beep
End If
Application.EnableEvents = True
End Sub
|
评分
-
1
查看全部评分
-
|