Search and Replace a number of characters in Excel using VBscript

0
0

I need to search and to replace a specific part of a string in an Excel sheet.

Here is my code and I don’t know how I can exactly search this part in each Cell.value.

my_new_string = "abc"
For each objSheet1 in objworkbook2.sheets
If objSheet1.Name = "Name1" Then
LastRow
= objsheet1.UsedRange.Rows.Count + objsheet1.UsedRange.Row - 1
For i = 1 To LastRow Step 1
For j = 1 To 15 Step 1
If objExcel1.Cells(i, j).value = "xyz" Then 'Here I have to check if the Cell value contains xyz and to replace it by **my_new_string**
End if
Next
Next
End If
Next

Any help please ?

  • You must to post comments
0
0

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
  • You must to post comments
Showing 1 result
Your Answer
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website