VBA tutorial: Method for string search and cell value replacement between Excel and text files-Office Software-php.cn

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

WBOY

Release： 2024-01-23 23:45:31

forward

558 people have browsed it

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

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

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

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