Replacing strings within strings based on my array - getting object error

Question

Sub clean()

Dim x As Long, lastrow As Long
Dim ws As Worksheet
Dim CleanAry
Set ws = ThisWorkbook.Sheets("Sheet1")
'copy Column B to C
'Range("B1:B1048576").Copy Destination:=ws.Range("C1:C1048576")

'array for clean up

CleanAry = Array("..", "__")

lastrow = ws.Range("C1048576").End(xlUp).Row

For Each cel In Range("C1:C" & lastrow)
    For i = LBound(CleanAry) To UBound(CleanAry)
            cel = cel.Replace(What:=CleanAry(i), _
                            Replacement:=".", _
                            Lookat:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False)
    Next i
Next cel
End Sub

I am trying to create an array of string combinations that will parse through a particular column and remove those string combinations from the existing strings in the cells.

However, my code only seems to work when I have one string in my array, and not when I add additional strings. I get the error :

"object required" for the "cel=..." section.

Haven't really coded in VBA consistently. Do help me figure out what is going wrong.

Not sure what you are facing, but Range.Replace method returns a boolean. Don't use your target cell to catch it since you need the value in target cell. — newacc2240
– newacc2240, Commented Nov 10, 2017 at 18:01
Another approach is cel.Value = Replace(cel.Value, CleanAry(i), ".") — newacc2240
– newacc2240, Commented Nov 10, 2017 at 18:02

Tim Williams · Accepted Answer · 2017-11-10 19:34:22Z

0

You should probably add some checks if you expect there could ever be errors in the range being cleaned. The whole process would be much faster using an array.

Sub clean()

    Dim x As Long, lastrow As Long
    Dim ws As Worksheet
    Dim CleanAry, v, arr, r As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    'copy Column B to C
    'Range("B1:B1048576").Copy Destination:=ws.Range("C1:C1048576")

    CleanAry = Array("..", "__")   'array for clean up

    lastrow = ws.Range("C1048576").End(xlUp).Row
    arr = Range("C1:C" & lastrow).Value
    For r = 1 to UBound(arr, 1)
        v = arr(r, 1)
        For i = LBound(CleanAry) To UBound(CleanAry)
                v = Replace(v, CleanAry(i), ".")
        Next i
        arr(r, 1) = v
    Next r
    Range("C1:C" & lastrow).Value = arr
End Sub

answered Nov 10, 2017 at 19:34

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replacing strings within strings based on my array - getting object error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related