|
发表于 2018-5-14 15:25:23
|
显示全部楼层
数组 户口练习
- Sub 户主()
- Dim arr(), brr(), crr()
- Dim sht1 As Worksheet, sht2 As Worksheet
- Dim i As Long, j As Long, k As Long, 行 As Long, 填 As Long, l As Long
- Set sht1 = Worksheets("sheet1")
- Set sht2 = Worksheets("sheet2")
- arr = sht1.Range("b1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- k = 1
- For i = 2 To UBound(arr)
- If arr(i, 2) = sht2.Range("c2") Then
- 行 = i
- End If
- If 行 <> 0 Then Exit For
- Next
-
- For i = 行 To UBound(arr)
- If arr(i, 2) = arr(i + 1, 2) Then
- sht2.Range("i4") = arr(i, 5)
- k = k + 1
- Else
- brr = Range(sht1.Cells(i - k + 1, 3), sht1.Cells(i, 7))
- Exit For
- End If
- Next
- 填 = 14
- crr = Array("本人", "妻", "长子", "长女", "次子", "次女", "三子", "三女", "儿媳", "父亲", "母亲", "兄弟", "姐妹")
- For l = 0 To UBound(crr)
- For i = 1 To UBound(brr)
-
- If crr(l) = brr(i, 2) Then
- 填 = 填 + 1
- sht2.Range("a" & 填) = brr(i, 1)
- sht2.Range("c" & 填) = brr(i, 2)
- sht2.Range("g" & 填) = brr(i, 3)
- End If
-
- Next
- Next
- End Sub
复制代码 |
|