|
本帖最后由 rosedowson 于 2015-4-2 16:28 编辑
有以下两个查询代码,还请老师解决一下,在数据表中单元格内如果含有#号,就查不到。如数据为“1#公交”,那么我用以下代码查询“1#公交”时则为空,要查“1[#]公交”才能查到,请问代码该如何优化呀,还请老师修改一下,谢谢啊一、单项查询
老师 我就没传附件了,因为公司安了加密系统,附件传不上来,麻烦您在这段代码上修改一下吧,谢谢呀
加上这条代码, Range("A5,B5").Replace "#", "[#]" 就可以查询到,但是会把单元格 A5、B5的值由#改为[#],能不能查询的时候加上[],但是不在A5、B5上显示出来了??
Private Sub CommandButton1_Click()
If Trim([a5] & [B5]) = "" Then Exit Sub
On Error Resume Next
With Sheets("数据表")
r = .Range("F2", .[a40000].End(3))
End With
a = [a5]
b = [B5]
s1 = "*" & a & "*"
s2 = "*" & b & "*"
For i = 1 To UBound(r)
If r(i, 2) Like s1 Then
If (r(i, 1) Like s2 Or r(i, 3) Like s2) Then
N = N + 1
For j = 1 To 6
r(N, j) = r(i, j)
Next
End If
End If
Next
If N Then
[E5:S150000] = ""
[e5].Resize(N, 6) = r
End If
End Sub
二、多项查询
Private Sub CommandButton2_Click()
If WorksheetFunction.CountA([a37:c56]) = 0 Then Exit Sub
Dim arr, brr
brr = Sheets("数据表").UsedRange
For b = 2 To UBound(brr)
brr(b, 1) = brr(b, 1) & "|" & brr(b, 2) & "|" & brr(b, 3) & "|" & brr(b, 4) & "|" & brr(b, 5) & "|" & brr(b, 6)
Next
arr = [a37:c56]
r = 5
For a = 1 To UBound(arr)
tmp$ = "*" & arr(a, 1) & "*" & arr(a, 2) & "*" & arr(a, 3) & "*"
If Len(tmp) > 4 Then
For b = 2 To UBound(brr)
If brr(b, 1) Like tmp Then Cells(r, "e").Resize(1, 6) = Split(brr(b, 1), "|"): r = r + 1
Next
End If
Next
Range("E5").Select
End Sub
|
|