CODEWITHSUNDEEP
excelvba
Zanam
Zanam

Reputation: 4807

Replacing all values in a column from csv file using VBA

I have a csv file with mix of string and numbers in different columns. I want to replace all "?" characters form column C.

I am trying the following:

Set wb_dst = Workbooks.Open("C:\myFile.csv")
Set ws_dst = wb_dst.Sheets(1)
values = ws_dst.Range("C1:c500").Value
values = Replace(values, "?", "")

But not sure how to dump this value back to column C without replacing values in other columns. Or, is there any other way to perform the above task or replacing certain characters only in a certain column from a csv file on local drive?

Edit: I am looking to update the values in column C for myFile.csv so I suppose I have to dump data to column C, I am not sure?

Upvotes: 0

Views: 1702

Answers (1)

Variatus
Variatus

Reputation: 14383

It's simple. You just assign the array you took from the range back to the value of that same range after modification. However, the idea of values = Replace(values, "?", "") won't fly. You will need to modify each element of the array individually.

Sub Macro1()

    Const LookFor       As String = "?"
    Const Replacement   As String = "X"
    
    Dim Wb              As Workbook
    Dim Ws              As Worksheet
    Dim Rng             As Range
    Dim Arr             As Variant
    Dim R               As Long                 ' loop counter: rows
    
    Set Wb = Workbooks.Open("C:\myFile.csv")
    Set Ws = Wb.Worksheets(1)
    Set Rng = Ws.Range("C1:C500")
    
    Arr = Rng.Value
    For R = 1 To UBound(Arr)
        Arr(R, 1) = Replace(Arr(R, 1), LookFor, Replacement)
    Next R
    Rng.Value = Arr
End Sub

Upvotes: 1

Related Questions