使用VBscript在Excel中搜索和替换多个字符-SearchandReplaceanumberofcharactersinExcelusingVBscript

3  

Thank you all,

谢谢你们，

this is working fine for me.

这对我来说很好。

For Each objsheet1 In objworkbook2.Sheets
    With objsheet1
        If .Name = "BatchRun" Then
            On error resume next
            For i = 1 To 15 Step 1
                For j = 1 To 10 Step 1
                    If InStr(1, .Cells(i, j).Value, my_old_string) > 0 Then
                        .Cells(i, j).Value = Replace(.Cells(i, j).Value, my_old_string, my_new_string)
                    End If
                Next
            Next 
        End If
    End with
Next

2  

I changed your method for finding the last row to one that is much more reliable.

我将找到最后一行的方法改为更可靠的方法。

Also you used 2 different objects to describe the same sheet, so I fixed it! ;)

您还使用了2个不同的对象来描述同一张纸，所以我修复了它！ ;）

Finally, you just need to use Replace method that will do the job perfectly fine, without need to test if the string is present with Instr (use it if you anything else to do if the old_string is detected)

最后，您只需要使用可以完美地完成工作的Replace方法，而无需测试Instr是否存在该字符串（如果检测到old_string，则在使用其他任何其他操作时使用它）

Const my_old_string = "xyz"
Const my_new_string = "abc"
Const xlPart = 2
Const xlFormulas = -4123
Const xlByRows = 1
Const xlPrevious = 2


For Each objsheet1 In objworkbook2.Sheets
    With objsheet1
    If .Name = "Name1" Then
        LastRow = .Cells.Find("*",.Range("A1"),xlPart,xlFormulas,xlByRows,xlPrevious,False).Row
        For i = 1 To LastRow Step 1
            For j = 1 To 15 Step 1
                .Cells(i, j).Value = Replace(.Cells(i, j).Value, my_old_string, my_new_string)
'                If InStr(1, .Cells(i, j).Value, my_old_string) Then
'                    .Cells(i, j).Value = Replace(.Cells(i, j).Value, my_old_string, my_new_string)
'                End If
            Next
        Next 
    End If
    End With
Next