|
1.为何关运行代码后身份证号查找出来最后三位都为零,每户对应的身份证号能否通过按户主身份证号为基准去调用整户的成员信息;
把格式全部變為「文字」即可,下面代碼已用身分證為基準(key)
2.如何将图片中的联系电话和银行账号加到代码中,匹配到值则返回值,匹配不到值则返回空格,留待手动打上去;
參考下面代碼
3.数据如果增加到几千行,这个代码如何改动,主要是我不理解代码含义,如果加以解释会更好。
完全不用動,代碼自動適應
Sub demo()
Application.Calculation = xlManual
Set d = CreateObject("Scripting.Dictionary")
a = Sheets(2).UsedRange
For i = 4 To UBound(a)
If a(i, 3) = a(i, 4) Then
Key = a(i, 6) '身分證為 key
d(Key) = i
End If
If i < UBound(a) Then
If a(i + 1, 3) = a(i + 1, 4) Then e = 1 Else e = 0
Else
e = 1
End If
If e Then d(Key) = d(Key) & " " & i
Next
b = Sheets(1).UsedRange
i = 0
With Sheets(1)
While i < UBound(b)
Key = b(i + 4, 3)
n = Split(d(Key))
.Cells(i + 6, 6) = a(n(0), 13) '電話
.Cells(i + 8, 4) = a(n(0), 12) '帳號
r = i + 12
For k = n(0) To n(1)
r = r + 1
.Cells(r, "b") = a(k, 4)
.Cells(r, "c") = a(k, 7)
.Cells(r, "d") = a(k, 6)
.Cells(r, "e") = a(k, 9)
Next
i = i + 24
Wend
End With
Application.Calculation = xlAutomatic
End Sub
祝順心,南無阿彌陀佛!
|
|