VBA中按T列关键字查询数据,能对数据进行筛选,但是身份证号列出错,筛选后的数据不是文本,导致最后四位数值为0,请问让身份号列格式不出错!
代码如下:
Sub 按T关键字筛选() Set d =CreateObject("scripting.dictionary") Dimcrr(1 To 55000, 1 To 18) With Sheets(“数据结果”) irow = .Cells(.Rows.Count, 20).End(xlUp).Row arr = .Range("t1:t" & irow) End With With Sheets("数据源") imax = .Cells(.Rows.Count, 1).End(xlUp).Row brr = .Range("A2:R" & imax) End With For K = 2 To UBound(arr) For n = 1 To UBound(brr) If InStr(brr(n, 1), arr(K, 1)) > 0 Then d(arr(K, 1)) = d(arr(K, 1)) + 1 m = m + 1 crr(m, 1) = brr(n, 1) crr(m, 2) = brr(n, 2) crr(m, 3) = brr(n, 3) crr(m, 4) = brr(n, 4) crr(m, 5) = brr(n, 5) crr(m, 6) = brr(n, 6) crr(m, 7) = brr(n, 7) crr(m, 8) = brr(n, 8) crr(m, 9) = brr(n, 9) crr(m, 10) = brr(n, 10) crr(m, 11) = brr(n, 11) crr(m, 12) = brr(n, 12) crr(m, 13) = brr(n, 13) crr(m, 14) = brr(n, 14) crr(m, 15) = brr(n, 15) crr(m, 16) = brr(n, 16) crr(m, 17) = brr(n, 17) crr(m, 18) = brr(n, 18) End If Next Next d_n = d.items d_k = d.keys Sheets(“数据结果”).Range("A2:r45000") = "" Sheets(“数据结果”).Range("t2:u" & irow) = "" Sheets(“数据结果”).Range("A2").Resize(m, 18) = crr Sheets(“数据结果”).Range("t2").Resize(d.Count, 1) = Application.Transpose(d_k) Sheets(“数据结果”).Range("u2").Resize(d.Count, 1) =Application.Transpose(d_n) MsgBox "完成", 0 + 64, "提示" End Sub
|