Results 1 to 8 of 8

Thread: Search and Replace custom function issue
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search and Replace custom function issue

    Good morning, I'm trying to write a function to search and replace, thus:
    Code:
    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
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  2. #2
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search and Replace custom function issue

    Sorry, just realised this should be:
    Code:
    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
    Last edited by NewOrderFac33; Sep 12th, 2019 at 04:23 AM.
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,814
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Search and Replace custom function issue

    Is Col_AnimalType more than one cell?

  4. #4
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search and Replace custom function issue

    Hi, Rory - yes - there are about 12,000 cells within the range.
    Pete
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  5. #5
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Search and Replace custom function issue

    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.

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

  6. #6
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search and Replace custom function issue

    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.
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,814
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Search and Replace custom function issue

    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.

  8. #8
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search and Replace custom function issue

    OK - thank you!
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •