CODEWITHSUNDEEP
excelvba
Jak Carty
Jak Carty

Reputation: 57

Find and replace, where not all entries exist

I need to Replace entries in Array1 = Array("11","15", "13") with Array2 = Array("a", "b", "ZYZ"). My target workbook is a csv file. For some reason the below code doesn't work.

Array1 = Array("11", "15", "13")
Array2 = Array("a", "b", "ZYZ")

For i = 1 To 3
    With TargetWB
        .Cells.Replace What:=Array1(i), Replacement:=Array2(i), _
        LookAt:=xlWhole, SearchOrder:=xlByColumns
    End With
Next i

My data is a CSV file that looks like this. Note that this file changes, so sometimes includes the '15' entry.

enter image description here

Upvotes: 0

Views: 27

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

For some reason the below code doesn't work.

Since you have not mentioned what is the problem, I can only guess the problem that you are facing but anyways, making these changes makes the code work for me.

1. Do not know how have you defined the array but change For i = 1 To 3 to For i = 0 To 2

2. Change TargetWB to Sheet object if that is a workbook. Something like TargetWB.Sheets(1)

Tested Code

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim Array1 As Variant
    Dim Array2 As Variant
    
    Array1 = Array("11", "15", "13")
    Array2 = Array("a", "b", "ZYZ")
    
    Dim TargetWB As Workbook
    Dim TargetWs As Worksheet
    
    '~~> Change this to the relevant csv
    Set TargetWB = ThisWorkbook
    Set TargetWs = TargetWB.Sheets(1)
    
    Dim i As Long
    
    For i = 0 To 2
        TargetWs.Cells.Replace What:=Array1(i), Replacement:=Array2(i), _
        LookAt:=xlWhole, SearchOrder:=xlByColumns
    Next i
End Sub

In Action

enter image description here

Upvotes: 1

Related Questions