Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Find and Replace any characters in a list of characters

  1. #11
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    ok thanks, will give that a try

  2. #12
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Quote Originally Posted by Fluff View Post
    Copy the function from post#2 to a module & then change this line
    Code:
    Sheets.Add.Name = a(p, 1)
    to
    Code:
       Sheets.Add.Name = ValidWBName(a(p, 1))
    That way you don't need to change the values in the sheet.
    ok thanks, will give that a try

  3. #13
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Quote Originally Posted by Fluff View Post
    Copy the function from post#2 to a module & then change this line
    Code:
    Sheets.Add.Name = a(p, 1)
    to
    Code:
       Sheets.Add.Name = ValidWBName(a(p, 1))
    That way you don't need to change the values in the sheet.
    i get a compile error "by ref argument type mismatch" on that line highlighting the letter 'a'

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Oops, it should be
    Code:
       Sheets.Add.Name = ValidWBName(CStr(a(p, 1)))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Quote Originally Posted by Fluff View Post
    Oops, it should be
    Code:
       Sheets.Add.Name = ValidWBName(CStr(a(p, 1)))

    I am now getting an application defined or object defined error.
    I think it is something to do with tha data rather than the code
    It is creating all of the new sheets, but is also creating a sheet with all of the values in and one with no values in (or it is freezing before it fills it)
    Perhaps that column need to be cleaned and trimmed in advance. it is contiguous but the cells contain some spaces at the end. Perhaps there is something else.

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Ok, how about replacing you code in post#10 with
    Code:
       Dim Ary As Variant
       Ary = Array("\", "/", ",", ".", ":", ";")
       For i = 0 To UBound(Ary)
          Range("I:I").Replace Ary(i), "_", xlPart, , , , False, False
       Next i
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Quote Originally Posted by Fluff View Post
    Ok, how about replacing you code in post#10 with
    Code:
       Dim Ary As Variant
       Ary = Array("\", "/", ",", ".", ":", ";")
       For i = 0 To UBound(Ary)
          Range("I:I").Replace Ary(i), "_", xlPart, , , , False, False
       Next i
    Thats nice, yea, works fine thanks.

    Rather than clean and trim the column i just used the above and replaced with "" rather than "_".

    The error i mentioned previously was caused by the totals row. The Object defined error. That as caused by the totals row.

    I really appreciate your help once again Fluff.
    My job isn't supposed to be about this, so that why I have a client and yet, clearly don't know how to do this.
    I am picking it up as I go, but I feel that I am often using things and not fully understanding how it is working. Can you recommend some reading? A book? By the end of the day I am screen blind.

    Thanks again.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    You're welcome & thanks for the feedback.

    I've never read any books on Excel/VBA so cannot recommend anything personally, but It's worth looking at anything by Bill Jelen (aka MrExcel) or John Walkenbach
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Aug 2019
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    Quote Originally Posted by Fluff View Post
    You're welcome & thanks for the feedback.

    I've never read any books on Excel/VBA so cannot recommend anything personally, but It's worth looking at anything by Bill Jelen (aka MrExcel) or John Walkenbach
    Thanks

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,062
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Find and Replace any characters in a list of characters

    My pleasure
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •