Search and Replace a number of characters in Excel using VBscript

Question

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 ?

If you don't want an "exact" match on the cell use `Instr(1, objExcel1.Cells(i, j).value, "xyz") > 0` as your condition to check for. Then use `Replace(objExcel1.Cells(i, j).value, "xyz", my_new_string)`. — user692942, Oct 10 '16 at 09:38
this is my condition and instruction If Instr(1, objExcel1.Cells(i, j).value, old_string) > 0 Then Replace(objExcel1.Cells(i,j).value, old_string, new_string) but I got this error " The test run cannot continue due to a syntax error. Cannot use parentheses when calling a Sub Line (74): "Replace(objExcel1.Cells(i,j).value, old_string, new_string)". " — sourh, Oct 10 '16 at 10:02
Your string replace has to replace something... so `objExcel1.Cells(i, j).value = Replace(Replace(objExcel1.Cells(i, j).value, "xyz", my_new_string)`. — user692942, Oct 10 '16 at 10:50
@Slai don't forget to you'll need [`Constant xlPart = 2`](https://msdn.microsoft.com/en-us/library/office/ff823160.aspx). — user692942, Oct 10 '16 at 11:26
`objworkbook2.Sheets("Name1").UsedRange.Replace "xyz", "my_new_string", 2` ( or `, 1` for `xlWhole`) thanks @Lankymart — Slai, Oct 10 '16 at 11:32
@Slai using `Constant name = value` syntax to declare the values is far better then just trying the numeric values though, that way the code doesn't need to change. — user692942, Oct 10 '16 at 11:45

score 3 · Answer 1 · edited Jan 31 '17 at 15:59

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

score 2 · Answer 2 · edited Sep 21 '17 at 17:20

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! ;)

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)

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

edited Sep 21 '17 at 17:20

Graham

7,431
18
59
84

answered Oct 10 '16 at 09:57

R3uK

14,417
7
43
77

I got this error "The test run cannot continue due to a syntax error. Expected ')' Line (72): "LastRow = .Cells.Find(What:="*", _". " – sourh Oct 10 '16 at 10:16
3

VBScript doesn't support `Parameter:=value` syntax. Just pass the values in order `.Find("*", .Range("A1"), ...` etc. Also `Next objsheet1` will break in VBScript use `Next`. – user692942 Oct 10 '16 at 11:22
Plus any named constant will need declaring before you can use them, look them up from here - [Range.Find Method (Excel)](https://msdn.microsoft.com/en-us/library/office/ff839746.aspx). – user692942 Oct 10 '16 at 11:28
@Lankymart : Thx, didn't know that and I just thought that the OP was using VBA and made an error on VBS! Made the corrections! ;) – R3uK Oct 10 '16 at 12:37
@sourh : Take a look at the edit! Thx to Lankymart, that should be working! ;) – R3uK Oct 10 '16 at 12:38
It gives me many errors "Type mismatch" for all the constant and being out of Range too. – sourh Oct 10 '16 at 13:05
1

You need to [edit] the question and show us what you have written if you are getting type "type mismatch" you likely have written the code in the wrong place and missed something. The constants need ideally to be declared in the global scope, so if this code is inside a `If` statement for example you might get an error. – user692942 Oct 10 '16 at 13:08
yes, my code is inside a big If statement, I will declare them in the global scope and i will be back to you. I'm learning a lot from your help !! – sourh Oct 10 '16 at 13:12
I'm getting the same Errors. Any way your first solution @Lankymart is working. I will see what is wrong with this one and why i'm getting type mismatch for the constants – sourh Oct 10 '16 at 13:19
@sourh not sure why they've added `my_old_string` and `my_new_string`??. They're only needed for the named constants from the Excel Object Library. – user692942 Oct 10 '16 at 13:34
I tried without considering my_old_string and my_new_string as constants and it's not working, maybe I'm missing some libraries. I didn't use constants in my test automation before this time. Do I need specific libraries for constants ? – sourh Oct 10 '16 at 13:38
2

I find it, we use Const and not Constant, Const is accepted by UFT. – sourh Oct 10 '16 at 13:43
@R3uK, your code is missing the `End With` statement – Victor Moraes Oct 12 '16 at 19:09
@VictorMoraes : Thx for pointing it out, I just corrected it! ;) – R3uK Oct 13 '16 at 07:21

Search and Replace a number of characters in Excel using VBscript

2 Answers2

Linked