CODEWITHSUNDEEP
vbaexcelloopsfor-loop
lydias
lydias

Reputation: 841

VBA Replace() in a loop with type mismatch error

I'm fairly new to VBA, and I'm just trying to replace any character, "NA", "ZZ", "Z", in column "N" and "O" with "0". The following is a loop with if statement, but for some reason I kept on getting

runtime 13 error with type mismatch

Could anyone help me to fix the error?

Thanks!

Dim v as String
sym = Array("NA", "ZZ", "Z")

  If Worksheets("Sheet1").Columns("N") Then
   For Each a In sym
    v = Replace(v, a, "0")
   Next a

 ElseIf Worksheets("Sheet1").Columns("O") Then
   For Each a In sym
     v = Replace(v, a, "0")
   Next a

 End If

Upvotes: 0

Views: 659

Answers (3)

warner_sc
warner_sc

Reputation: 848

Try this:

Sub test()

Dim sym As Variant
sym = Array("NA", "ZZ", "Z")

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

Dim colums_range As Range

With ws
Set colums_range = Union(.Columns("N").SpecialCells(xlCellTypeConstants), .Columns("O").SpecialCells(xlCellTypeConstants))
End With

   For Each element In sym

    colums_range.Replace What:=CStr(element), Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False

   Next element

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You don't need to loop over the cells, try:

Sub lydias()
    With Sheets("Sheet1").Range("N:O")
        .Replace What:="NA", replacement:="0"
        .Replace What:="ZZ", replacement:="0"
        .Replace What:="Z", replacement:="0"
    End With
End Sub

Upvotes: 3

SJR
SJR

Reputation: 23081

You can do it like so. It's good practice to specify the various parameters.

sym = Array("NA", "ZZ", "Z")

With Worksheets("Sheet1").Range("N:O")
    For Each a In sym
        .Cells.Replace what:=a, replacement:=0
   Next a
End With

Upvotes: 4

Related Questions