|
出问题的原因是B19单元格有内容,而其后面都是空的,所以无法处理房号分解。
不完整的数据总会出现各种各样的问题,如果你不想以B列作为判断依据,而是以D列来判断的话,改下代码就行。
至于代码合并,你可以自己试下,除了变量可能会有冲突之外,单元格区域操作是没有问题的。
- Private Sub CommandButton2_Click()
- Dim n&, s$, i&, j&, k&, arr, brr, crr, r&
- arr = [b17:ad20]
- For i = 1 To UBound(arr)
- If arr(i, 3) = "" Then Exit For
- brr = Split(arr(i, 3), "-")
- n = n + (brr(1) - brr(0)) / 100 + 1
- Next i
- ReDim brr(1 To n, 1 To UBound(arr, 2))
- For i = 1 To UBound(arr)
- If arr(i, 3) = "" Then Exit For
- crr = Split(arr(i, 3), "-")
- For j = crr(0) To crr(1) Step 100
- r = r + 1
- For k = 1 To UBound(arr, 2)
- brr(r, k) = arr(i, k)
- Next k
- brr(r, 3) = j
- brr(r, 4) = Int(j / 100)
- Next j
- Next i
- [c27].Resize(r, UBound(brr, 2)) = brr
- End Sub
复制代码 |
|