首页 >软件教程 >办公软件 >VBA教程：在Excel和文本文件之间进行字符串搜索和单元格值替换的方法

VBA教程：在Excel和文本文件之间进行字符串搜索和单元格值替换的方法

WBOY转载: 2024-01-23 23:45:31595浏览

如果你的txt文件使用tab分隔符，那么以下代码可以符合你的要

Sub ChangeTxt()

Dim FileN, TxtWb As Workbook, ToFindData As String

Dim ToSubData As String, c As Range, FirstAdr As String

If MsgBox（＂是否已经选中待查找的单元格？＂， vbYesNo） = vbNo Then Exit Sub

ToFindData = Selection.Cells(1).Value

ToSubData = Selection.Cells(1).Offset(, 1).Value

FileN = Application.GetOpenFilename(＂Txt文件，*.txt＂，，＂选择txt文件＂)

If TypeName(FileN) = ＂Boolean＂ Then Exit Sub

Workbooks.OpenText Filename:=FileN, consecutivedelimiter:=False, _

Tab:=True, Space:=False

Set TxtWb = ActiveWorkbook

Set c = TxtWb.Sheets(1).UsedRange.Find(What:=ToFindData, _

LookAt:=xlPart, MatchCase:=False, matchbyte:=False, SearchFormat:=False)

If Not c Is Nothing Then

FirstAdr = c.Address

c.Offset(, 4) = ToSubData

Set c = TxtWb.Sheets(1).UsedRange.FindNext(c)

Loop Until c.Address = FirstAdr

TxtWb.Close savechanges:=True

MsgBox ＂替换完毕＂

Else

TxtWb.Close False

MsgBox ＂未找到，请选中要查找的单元格。＂

End If

Set c = Nothing

Set TxtWb = Nothing

End Sub

excel vba内容替换

Sub s()

Dim c As Range

n = Cells(Rows.Count, ＂t＂).End(3).Row

Set rg = Range(＂b6:t＂ & n)

rg.HorizontalAlignment = xlCenter

t = InputBox（＂输入要查找的数字＂）

For Each c In rg

If c ＂＂ And c ＂√＂ And c ＂X＂ Then

If InStr(c, t) >0 Then

c = ＂√＂

Else

c = ＂X＂

End If

For Each c In rg

If c = ＂√＂ Then

c.Font.Bold = True

c.Font.Color = vbBlue

ElseIf c = ＂X＂ Then

c.Font.Bold = True

c.Font.Color = vbRed

End If

End Sub

遍历工作表查找字符串并替换 VBA

Range(＂A1＂).Select

Selection.Copy

Cells.Find(What：=＂*照明*＂， After:=ActiveCell, LookIn:=xlFormulas, LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, MatchByte:=False, SearchFormat:=False).Activate