Search and Replace custom function issue

[NewOrderFac33]

Good morning, I'm trying to write a function to search and replace, thus:

Set AnimalColumn = Sheets("Database").Range("Col_AnimalType")
ReplaceWithSpecies AnimalColumn

Function ReplaceWithSpecies(RangeToProcess As Range)
    With RangeToProcess
        Select Case UCase(.Formula)
            Case Is = "Tiger", "Lion"
                .Formula = "Cat"
            Case "Labrador", "Alsatian"
                .Formula = "Dog"
            Case Else
                .Formula = "Fish"
        End Select
    End With
End Function

but when I attempt to run my code. I get "Runtime error 13 Type Mismatch" with the "Select Case" line highlighted.

Can anyone suggest where I'm going wrong, please?

Thanks in advance - Pete

 

[NewOrderFac33]

Sorry, just realised this should be:

Set AnimalColumn = Sheets("Database").Range("Col_AnimalType")
ReplaceWithSpecies AnimalColumn

Function ReplaceWithSpecies(RangeToProcess As Range)
    With RangeToProcess
        Select Case UCase(.Formula)
            Case Is = "TIGER", "LION"
                .Formula = "Cat"
            Case "LABRADOR", "ALSATIAN"
                .Formula = "Dog"
            Case Else
                .Formula = "Fish"
        End Select
    End With
End Function

but it still returns the same error!
Also, all the values in the worksheet range being processed are text - no numeric values.
Pete

 

[RoryA]

Is Col_AnimalType more than one cell?

 

[NewOrderFac33]

Hi, Rory - yes - there are about 12,000 cells within the range.
Pete

 

[Akuini]

You need to loop each cell in the "Col_AnimalType", and pass the cell as the argument.
And since you have ~12K cells then it's better to turn off Application.ScreenUpdating.

Dim c As Range
Application.ScreenUpdating = False
    For Each c In Sheets("Database").Range("Col_AnimalType")
        ReplaceWithSpecies (c)
    Next
Application.ScreenUpdating = True

 

[NewOrderFac33]

Hi, Akuini - thanks for your reply - although your suggestion would work, I'm currently trying to avoid For Each loops as there are another 10 similar operations that need to be performed on my workbook and they can be quite slow.

 

[RoryA]

You're going to need a loop of some kind. I suggest you load the range into an array and loop through that, then write the array back to the sheet.

 

[NewOrderFac33]

OK - thank you!