CODEWITHSUNDEEP
excelvbareplace
user3648971
user3648971

Reputation: 101

How to replace string in VB?

i have problem when try to replacing String

tmpStr = "['common_follow'] = '#1' " & vbCr _
       & "['common_cancel'] = '#10' "

For i = 1 To rng.Rows.Count
    cellValue = rng.Cells(i, 4).Value
    'Debug.Print (cellValue)    
    tmp = "#" & i
    'Debug.Print (tmp)

    tmpStr = Replace(tmpStr, tmp, cellValue)
    Debug.Print (tmpStr)
Next i

#1 replace to ['common_follow'] = 'follow' => good

#10 replace to ['common_cancel'] = 'follow0' => #10 replace is not recognized -> #1 only recognize

How do you handle this?

Upvotes: 1

Views: 43

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Replace #10 first then #1 will solve the problem.

Therefore loop backwards:

Dim TmpStr As String
TmpStr = "['common_follow'] = '#1' " & vbCr _
   & "['common_cancel'] = '#10' "

Dim i As Long
For i = Rng.Rows.Count To 1 Step -1 'backwards
    Dim CellValue As Variant
    CellValue = Rng.Cells(i, 4).Value
    'Debug.Print CellValue

    Dim Tmp As String
    Tmp = "#" & i
    'Debug.Print Tmp

    TmpStr = Replace$(TmpStr, Tmp, CellValue)
    Debug.Print TmpStr
Next i

Alternatively you need to replace including the surounding single quotes ' '

Dim TmpStr As String
TmpStr = "['common_follow'] = '#1' " & vbCr _
   & "['common_cancel'] = '#10' "

Dim i As Long
For i = 1 To Rng.Rows.Count 'forward
    Dim CellValue As Variant
    CellValue = Rng.Cells(i, 4).Value
    'Debug.Print CellValue

    Dim Tmp As String
    Tmp = "'#" & i & "'" 'replace with quotes '#1'
    'Debug.Print Tmp

    TmpStr = Replace$(TmpStr, Tmp, "'" & CellValue & "'") 'add quotes to the value
    Debug.Print TmpStr
Next i

Upvotes: 1

Related Questions