+ Reply to Thread
Results 1 to 5 of 5

Using wildcards with UDF that uses Replace

Hybrid View

  1. #1
    Registered User
    Join Date
    02-12-2018
    Location
    Mpls, USA
    MS-Off Ver
    360 2016
    Posts
    11

    Using wildcards with UDF that uses Replace

    Hello, my first post so I hope I am doing this correctly

    I have the Function below that finds the nth occurrence of a substring in a string and it works great, but I will not take wildcards

    And I desperately need to use it with wildcards

    Can anyone see how to alter the function or the way I am using it that will permit me to use it using wildcards?

    I have tried but have been no luck

    Thanks for considering my question

    Function

    Function ReplaceN(ByVal str1 As Variant, strFind As String, strReplace As String, N As Long, Optional Count As Long) As String
    Dim i As Long, j As Long
    Dim strM As String
    
      strM = str1
      If Count <= 0 Then Count = 1
        For i = 1 To N - 1
          j = InStr(1, strM, strFind)
          strM = Mid(strM, j + Len(strFind), Len(strM))
        Next i
      If N <= 0 Then
        ReplaceN = str1
      Else
        ReplaceN = Mid(str1, 1, Len(str1) - Len(strM)) & Replace(strM, strFind, strReplace, Start:=1, Count:=Count)
      End If
      
    End Function
    How I am calling it

    Sub ReplaceNthInstance()
    Dim ws As Worksheet
    Dim outArray As Variant
    Dim i As Long, LR As Long
    
    Set ws = ThisWorkbook.Sheets("Z")
    
    With ws.Range("E:E")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            outArray = .value
            For i = 1 To .Rows.Count
                outArray(i, 1) = ReplaceN(.Cells(i, 1).value, "[*]", vbNullString, 1)
            Next i
            .value = outArray
        End With
    End With
    End Sub
    Last edited by Einarr; 02-12-2018 at 11:55 AM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Using wildcards with UDF that uses Replace

    Welcome to the forum
    Something happened when you pasted your function - it is not complete
    Please find the rest of it, test it, and then replace the VBA above with complete, working function - thanks

    should be on 2 lines:

    Dim ws As WorksheetDim i As Long, j As Long, LR As Long

    where is the start of this loop ?
    Next r
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    02-12-2018
    Location
    Mpls, USA
    MS-Off Ver
    360 2016
    Posts
    11

    Re: Using wildcards with UDF that uses Replace

    Hello kev_, sorry about, I updated the function

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Using wildcards with UDF that uses Replace

    what does replacing wildcards mean

    if the cell was abcdefeghijkabcdefeghijk and you wanted to replace the 2nd def* with xyz what is the answer?

  5. #5
    Registered User
    Join Date
    02-12-2018
    Location
    Mpls, USA
    MS-Off Ver
    360 2016
    Posts
    11

    Re: Using wildcards with UDF that uses Replace

    Here is a common example of what I want to do, I have a column of data that looks like this

    What types of activities have you engaged in with [Field-3]? You can choose multiple answers. - What types of activities have you engaged in with [Field-3]? You can choose multiple answers. - Selected Choice
    What types of activities have you engaged in with [Field-4]? You can choose multiple answers. - What types of activities have you engaged in with [Field-4]? You can choose multiple answers. - Selected Choice
    What types of activities have you engaged in with [Field-5]? You can choose multiple answers. - What types of activities have you engaged in with [Field-5]? You can choose multiple answers. - Selected Choice
    etc.

    I want it to become this:
    What types of activities have you engaged in with [Field-3]
    What types of activities have you engaged in with [Field-4]
    What types of activities have you engaged in with [Field-5]
    etc.

    So I need to remove this:
    ? You can choose multiple answers. - What types of activities have you engaged in with[*]? You can choose multiple answers. - Selected Choice

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find and replace to not replace characters found as wildcards
    By sabutler4 in forum Excel General
    Replies: 4
    Last Post: 07-03-2013, 06:48 PM
  2. Find and Replace Wildcards
    By hisimage in forum Excel General
    Replies: 0
    Last Post: 01-08-2013, 05:25 PM
  3. Wildcards with Replace()
    By onidarbe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2009, 03:56 PM
  4. Using Wildcards in Find & Replace
    By uhcord98 in forum Excel General
    Replies: 13
    Last Post: 08-19-2007, 03:27 PM
  5. Replace with wildcards
    By rbmcclen in forum Excel General
    Replies: 2
    Last Post: 09-21-2006, 01:14 PM
  6. [SOLVED] Replace using Wildcards
    By Stella in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2006, 01:55 PM
  7. [SOLVED] wildcards in replace
    By ldashburnwest@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2005, 08:25 PM
  8. Replace using wildcards
    By jeb in forum Excel General
    Replies: 6
    Last Post: 01-06-2005, 12:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1