时间戳

首页 > 解决方案 > 如何在VB中替换字符串？

excel - 如何在VB中替换字符串？

问题描述

尝试替换字符串时出现问题

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替换为['common_follow'] = 'follow' => 好

#10替换为['common_cancel'] = 'follow0'=>#10无法识别替换 ->#1仅识别

你怎么处理这个？

标签： excelvbareplace

解决方案

先更换#10再#1解决问题。

因此向后循环：

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

或者，您需要替换包括周围的单引号' '

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

推荐阅读