Excel VBA - Find & Replace (Faster Method?)

[TropicalMagic]

Hi all,



I would like to find and replace all values, from “Viet Nam” to “Vietnam” in range A.



However, I found that finding and replacing values cell by cell is extremely slow, especially if the there are 1000+ rows in the range A

I would like to use Excel VBA and arrays as arrays speeds up the automation process greatly. I am also open to using scripting dictionaries.


Here is my worksheet layout:

Here is my code so far (does not work):



```

Dim ws As Worksheet

Dim x, LastRow As Long

Dim arrA As Variant



Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorksheet")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row



arrA = ws.Range("A2:A" & LastRow).Value 'put the range in an array to make the iteration faster



For x = 1 To UBound(arrA)



If arrA = “Viet Nam” Then

arrA(x, 1) = “Vietnam”

End If

Next x

```



How do I fix the issue?



Many thanks!

 

[JEC]

Select the column

- ctrl+H
- find what: Viet Nam
-Replace with: Vietnam

 

[johnnyL]

@TropicalMagic Here is your wish for vba approach with array:

Sub FindVietnam()
'
    Dim x       As Long
    Dim LastRow As Long
    Dim arrA()  As Variant
'
    LastRowColumnA = Sheets("MyWorksheet").Range("A" & Rows.Count).End(xlUp).Row
'
    arrA = Application.Transpose(Sheets("MyWorksheet").Range("A2:A" & LastRowColumnA))                  ' Load all country names into a 1D array
'
    For x = 1 To UBound(arrA)
        If StrComp(Replace(arrA(x), " ", ""), "Vietnam", vbTextCompare) = 0 Then arrA(x) = "Vietnam"    ' Compare to "Vietnam" with spaces removed & upper or lower case
    Next x
'
    Sheets("MyWorksheet").Range("A2:A" & LastRowColumnA) = Application.Transpose(arrA)                  ' Write array back to Column A
End Sub

 

[JEC]

This will do in VBA

Sub jec()
Range("A1", Cells(Rows.Count, 1).End(xlUp)).Replace "Viet Nam", "Vietnam"
End Sub

 

[TropicalMagic]

@TropicalMagic Here is your wish for vba approach with array:

Sub FindVietnam()
'
    Dim x       As Long
    Dim LastRow As Long
    Dim arrA()  As Variant
'
    LastRowColumnA = Sheets("MyWorksheet").Range("A" & Rows.Count).End(xlUp).Row
'
    arrA = Application.Transpose(Sheets("MyWorksheet").Range("A2:A" & LastRowColumnA))                  ' Load all country names into a 1D array
'
    For x = 1 To UBound(arrA)
        If StrComp(Replace(arrA(x), " ", ""), "Vietnam", vbTextCompare) = 0 Then arrA(x) = "Vietnam"    ' Compare to "Vietnam" with spaces removed & upper or lower case
    Next x
'
    Sheets("MyWorksheet").Range("A2:A" & LastRowColumnA) = Application.Transpose(arrA)                  ' Write array back to Column A
End Sub

This looks good! It worked as I intended! Many thanks!!

Also, thanks for deriving an array-based solution, it reduced the duration required!

 

[TropicalMagic]

This will do in VBA

Sub jec()
Range("A1", Cells(Rows.Count, 1).End(xlUp)).Replace "Viet Nam", "Vietnam"
End Sub

Thanks for your solution!

It works as well!

 

[johnnyL]

@TropicalMagic glad we could help.

 

[Michael M]

Ummm, why use VBA when a simple Find/Replace of column "A" would do ??....as suggested by @*JEC

 

[TropicalMagic]

Ummm, why use VBA when a simple Find/Replace of column "A" would do ??....as suggested by @*JEC

Hi, thanks for your query!

Well, this issue is a small part of a large and frequent (weekly) report generation. I'm not the owner of the main file which I obtain the data from, and the main file will always show entries as "Viet Nam".

Hence, I would prefer to use VBA to automate the manual, repetitive component of editing to "Vietnam", as well as reduce the duration required to run the automation by using arrays since the range can contain as many as 1000+ rows.

 

[Michael M]

That's OK...now that you have provided further information for the need to do it by VBA, it makes sense to do it that way...