How to use Find & Split & Replace/Join to revise part of specific string?

[Macaron]

Hi,

I am trying to revise part of string in excel,
but my code cannot produce my expectation.
Hope somebody can help.

Sub FindSplitReplace()
Dim sht As Worksheet
Dim wb As Workbook
Dim strBD As String
Dim arrSplitformat() As String
Dim rmkrng As Range
Dim Usedrng As Range
Dim Usedrmkrng As Range
Dim FirstAddr As String
Dim LastColumn As Long
Dim foundMCB As Range

MCBToFind = "MCB BD"

Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)
Set wb = ThisWorkbook
Set wf = WorksheetFunction
For i = 1 To ThisWorkbook.Sheets.Count
    
    Set sht = wb.Sheets(i)
    Set Usedrng = sht.UsedRange
Set rmkrng = Usedrng.Cells.Find(What:="Remark", LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, MatchByte:=False, SearchFormat:=False)
              LastColumn = rmkrng.Column

Set Usedrmkrng = sht.UsedRange.Columns(LastColumn).Resize(, 2)

Set foundMCB = Usedrmkrng.Cells.Find(What:=MCBToFind, After:=rmkrng, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        
    If Not foundMCB Is Nothing Then
        FirstAddr = foundMCB.Address
    End If
   
Do Until foundMCB Is Nothing
If Not foundMCB.Value Like "*TO '*" Then
arrSplitformat = Split(foundMCB.Value, " ")
strBD = arrSplitformat(0)
sht.Range(sht.Cells(foundMCB.Row), sht.Cells(foundMCB.Column)).Replace _
What:=strBD, Replacement:="TO ' & strBD & '", _
SearchOrder:=xlByColumns, MatchCase:=True
End If
Set foundMCB = Usedrmkrng.FindNext(After:=foundMCB)

        
        If foundMCB Is Nothing Then Exit Do
        If foundMCB.Address = FirstAddr Then Exit Do
    Loop
'-----------------------------------------------------
Next i
End Sub

Here is a part of my VBA code.
I would like to revise the first word that belongs to MCB BD. For Example, string "PA TPN MCB BD" changes to string "TO 'PA' TPN MCB BD", string "3TA SPN MCB BD" changes to string "TO '3TA' TPN MCB BD ,and so on. That means add "TO ' & strBD &'" to the first word. I have tried to use Replace and Join Function. But failed.

test test format1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
9
10
11																					Remark
12
13																					PA TPN MCB BD
14
15
16																					3TA SPN MCB BD
17																					SPACE
18																					SPACE
19																					2PB TPN MCB BD
20
21
22																					SPACE
23																					3LA SPN MCB BD
24																					SPACE
25																					3C TPN MCB BD
26
27
28																					7B TPN MCB BD
29
30
31																					PDC TPN MCB BD
32
33
34																					3LC TPN MCB BD
35
36
S3 (2)

 

[trunten]

I may be misunderstanding your problem but would this work for you?

Sub findAndPrepend()
    Dim sht As Worksheet, r As Range, s$, addr$
   
    s = "MCB BD"
    For Each sht In ThisWorkbook.Sheets
        Set r = sht.Cells.Find(s, sht.Range("A1"), xlValues, xlPart, xlByRows, xlNext, False)
        If Not r Is Nothing Then addr = r.Address
        While Not r Is Nothing
            If StrComp(Left(r.Value, 2), "TO", vbTextCompare) <> 0 Then r.Value = "TO '" & r.Value & "'"
            Set r = sht.Cells.FindNext(r)
            If r.Address = addr Then Set r = Nothing
        Wend
    Next sht
End Sub

 

[Macaron]

I may be misunderstanding your problem but would this work for you?

Sub findAndPrepend()
    Dim sht As Worksheet, r As Range, s$, addr$
  
    s = "MCB BD"
    For Each sht In ThisWorkbook.Sheets
        Set r = sht.Cells.Find(s, sht.Range("A1"), xlValues, xlPart, xlByRows, xlNext, False)
        If Not r Is Nothing Then addr = r.Address
        While Not r Is Nothing
            If StrComp(Left(r.Value, 2), "TO", vbTextCompare) <> 0 Then r.Value = "TO '" & r.Value & "'"
            Set r = sht.Cells.FindNext(r)
            If r.Address = addr Then Set r = Nothing
        Wend
    Next sht
End Sub

almost correct.

I want ' add to the right and left of first word such as TO 'PA' TPN MCB BD,
not the whole value, so I have to use split function to revise before.

 

[trunten]

Gotcha!

Sub findAndPrepend()
    Dim sht As Worksheet, r As Range, s$, addr$
  
    s = "MCB BD"
    For Each sht In ThisWorkbook.Sheets
        Set r = sht.Cells.Find(s, sht.Range("A1"), xlValues, xlPart, xlByRows, xlNext, False)
        If Not r Is Nothing Then addr = r.Address
        While Not r Is Nothing
            If StrComp(Left(r.Value, 2), "TO", vbTextCompare) <> 0 Then r.Value = "TO '" & Replace(r.Value, " ", "' ", Count:=1)
            Set r = sht.Cells.FindNext(r)
            If r.Address = addr Then Set r = Nothing
        Wend
    Next sht
End Sub

 

[Macaron]

Gotcha!

Sub findAndPrepend()
    Dim sht As Worksheet, r As Range, s$, addr$
 
    s = "MCB BD"
    For Each sht In ThisWorkbook.Sheets
        Set r = sht.Cells.Find(s, sht.Range("A1"), xlValues, xlPart, xlByRows, xlNext, False)
        If Not r Is Nothing Then addr = r.Address
        While Not r Is Nothing
            If StrComp(Left(r.Value, 2), "TO", vbTextCompare) <> 0 Then r.Value = "TO '" & Replace(r.Value, " ", "' ", Count:=1)
            Set r = sht.Cells.FindNext(r)
            If r.Address = addr Then Set r = Nothing
        Wend
    Next sht
End Sub

It works! Could you explain more about these code?
I don't understand ...
1. why add $ to s and addr.?
2. Does sht.Range("A1") for finding value in merged area?
3. what does Count:=1 mean?

 

[trunten]

It works! Could you explain more about these code?
I don't understand ...
1. why add $ to s and addr.?
2. Does sht.Range("A1") for finding value in merged area?
3. what does Count:=1 mean?

1. Me being lazy. It's the type-decleration character for a string:


2. Yes

3. It's an argument for the replace function. By default it replaces every instance of the character searched for. Setting the count parameter to 1 just means that it will only replace the first instance found. a 2 would have replaced the first 2 instances, and so on and so on.


If you're interested at all in the type characters, here's the ones i know about:

Type character	Data type	Example
%	Integer	Dim int%
&	Long	Dim lng&
@	Decimal	Dim dec@
!	Single	Dim sgl!
#	Double	Dim dbl#
$	String	Dim str$

 

[Macaron]

1. Me being lazy. It's the type-decleration character for a string:

View attachment 58842
2. Yes

3. It's an argument for the replace function. By default it replaces every instance of the character searched for. Setting the count parameter to 1 just means that it will only replace the first instance found. a 2 would have replaced the first 2 instances, and so on and so on.


If you're interested at all in the type characters, here's the ones i know about:

Type character	Data type	Example
%	Integer	Dim int%
&	Long	Dim lng&
@	Decimal	Dim dec@
!	Single	Dim sgl!
#	Double	Dim dbl#
$	String	Dim str$

Oh I see!! Thanks so much!