VBA tutorial: Method for string search and cell value replacement between Excel and text files

VBA Help: How to implement string search and cell value replacement between excel and txt

If your txt file uses tab delimiter, then the following code can meet your needs

Sub ChangeTxt()

Dim FileN, TxtWb As Workbook, ToFindData As String

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

If MsgBox ("Has the cell to be found been selected?", vbYesNo) = vbNo Then Exit Sub

ToFindData = Selection.Cells(1).Value

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

FileN = Application.GetOpenFilename("Txt file, *.txt", , "Select txt file")

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 "Replacement completed"

Else

TxtWb.Close False

MsgBox "Not found, please select the cell you want to find."

End If

Set c = Nothing

Set TxtWb = Nothing

End Sub

excel vba content replacement

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 ("Enter the number you want to find")

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

Next

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

Next

End Sub

Traverse the worksheet to find strings and replace VBA

Range(＂A1＂).Select

Selection.Copy

Cells.Find(What:="*Lighting*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

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

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

Cells.FindNext(After:=ActiveCell).Activate

Cells.FindNext(After:=ActiveCell).Activate

Cells.FindNext(After:=ActiveCell).Activate

Cells.FindNext(After:=ActiveCell).Activate

Cells.FindNext(After:=ActiveCell).Activate

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What:="*Lighting*", Replacement:="Many Company Lighting*30 times", LookAt:= _

xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What:="*Lighting*", Replacement:="Many Company Lighting*30 times", LookAt:= _

xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What:="*Lighting*", Replacement:="Many Company Lighting*30 times", LookAt:= _

xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What:="*Lighting*", Replacement:="Many Company Lighting*30 times", LookAt:= _

xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

ActiveCell.Replace What:="*Lighting*", Replacement:="Many Company Lighting*30 times", LookAt:= _

xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Cells.FindNext(After:=ActiveCell).Activate

The above is the detailed content of VBA tutorial: Method for string search and cell value replacement between Excel and text files. For more information, please follow other related articles on the PHP Chinese website!