第1个回答 2010-08-16
给你多个查询替换的例子看看,有用的话,给加分啊
'多字符替换vba程序
Public Sub Multi_replacement() '多个字符替换为一个
Dim IP As Variant
Dim OP As String
Dim TP As String
IP = Split(Application.InputBox("替换的字符,多个用|隔开"), "|")
OP = Application.InputBox("替换为")
TP = Application.InputBox("单元格内替换为Y,(xlPart),单元格整体替换为N(xlWhole)")
If TP = "N" Or TP = "" Then
TP = xlWhole
Else
TP = xlPart
End If
For i = LBound(IP) To UBound(IP)
'LookAt:=xlWhole,单元格匹配,LookAt:=xlPart为字符匹配,单元格内字符也会替换,MatchCase:=True是区分大小写
Cells.replace What:=IP(i), replacement:=OP, LookAt:=TP, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub本回答被提问者采纳
第2个回答 2010-08-16
给你2个实例
61、查找指定值
Sub 查找指定值()
Dim result As String, str1 As String, str2 As String
Dim c As Range
result = Application.InputBox(prompt:="请输入要查找的值:", Title:="查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With ActiveSheet.Cells
Set c = .Find(result, , , xlWhole, xlByColumns, xlNext, False)
If Not c Is Nothing Then
str1 = c.Address
Do
c.Interior.ColorIndex = 4 '加亮显示
str2 = str2 & c.Address & vbCrLf
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> str1
End If
End With
MsgBox "查找到指定数据在以下单元格中:" & vbCrLf & vbCrLf _
& str2, vbInformation + vbOKOnly, "查找结果"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
62、模糊查询
Sub 模糊查询()
Dim result As String, str1 As String
Dim c As Range, rng As Range
result = Application.InputBox(prompt:="请输入要查找的值:", _
Title:="模糊查找", Type:=2)
If result = "False" Or result = "" Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rng = ActiveSheet.Range("A1").CurrentRegion
str1 = "*" & result & "*"
For Each c In rng.Cells
If c.Value Like str1 Then
c.Interior.ColorIndex = 4
End If
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
第3个回答 2010-08-18
本程序是在sheet1中建立基础信息,在sheet2显示结果,在sheet2中的A2:I2中输入关键字,如表1中A列是姓名,在表2中A2输入张就姓张的全部显示在表2中,可以多条件,表1 中B列是部门,在表2中2输入财,部门名称中有财字的全部显示出来,基本上符合你说的要求
Sub 查询筛选()
Dim Erow As Integer
With Sheets("sheet2")
Erow = Sheets("sheet1").[a65536].End(xlUp).Row
.Range("a3:i65536").ClearContents
For Each ce In .[a2:i2]
If ce <> "" Then ce.Value = "*" & ce & "*" '加上通配符*,实现模糊查询
Next
Sheets("sheet1").Range("A2:I" & Erow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
.[a1:i2], CopyToRange:=.[A3], Unique:=False
For Each ce In .[a2:i2]
If ce <> "" Then ce.Value = Mid(ce, 2, Len(ce) - 2) '取消 "*"通配符
Next
End With
End Sub
第4个回答 2010-08-17
在VBA中用Sql查询就可以