首頁 >軟體教學 >辦公室軟體 >VBA教學：在Excel和文字檔案之間進行字串搜尋和儲存格值替換的方法

VBA教學：在Excel和文字檔案之間進行字串搜尋和儲存格值替換的方法

WBOY轉載: 2024-01-23 23:45:31594瀏覽

VBA助：如何實作excel與txt之間字串尋找與儲存格值的替換

如果你的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

##Do

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

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

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What：=＂*照明*＂， Replacement：=＂馬尼公司照明*30倍＂， LookAt:= _