+ Reply to Thread
Results 1 to 6 of 6

Finding characters not in VBA character set

  1. #1
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Finding characters not in VBA character set

    I have a macro that finds and replaces certain characters in my spreadsheets (double quotes to single quotes; accented characters to their unaccented equivalents). However there are Polish and Lithuanian accented characters that VBA can't find. For example, I would like to replace the Polish uppercase Ą (Latin A with an Ogonek, UTF-8 character decimal 260). I tried coding this as ChrW(U+260) in my findlist, but that didn't work. If someone can show me how to code it in the findlist, I can do the other characters I need. Here is my macro (Just for illustration, at the end I have added finding A and replacing it with ChrW(U+260). This works as expected, but is the reverse of what I need.):

    Sub RemoveSomeProhibitedCharacters()
    'PURPOSE: Find & Replace a list of prohibited characters throughout entire workbook
    'Includes quotes, umlauts, esszett, Lith S and Z

    Dim sht As Worksheet
    Dim fndList As Variant
    Dim rplcList As Variant
    Dim x As Long

    fndList = Array("""", "''", "", "", "", "", "", "", "", "", "", "", "", "A")
    rplcList = Array("''", "'", "A", "a", "O", "o", "U", "u", "ss", "S", "s", "Z", "z", ChrW(U + 260))

    'Loop through each item in Array lists
    For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Next sht

    Next x

    End Sub

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Finding characters not in VBA character set

    Hi,

    Ą is U+104, so you would use
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Re: Finding characters not in VBA character set

    Thanks for that.
    How do I modify the macro to enforce case matching - right now, chrw(&h104) finds both Ą and ą and replaces them with the same character. Ditto for the other upper-lower pairs in the find list.

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

    Re: Finding characters not in VBA character set

    deleted by kev
    better solution above
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Finding characters not in VBA character set

    You have
    Please Login or Register  to view this content.
    Change it to True.

  6. #6
    Registered User
    Join Date
    12-09-2017
    Location
    Ohio
    MS-Off Ver
    Excel 2013 (home/student)
    Posts
    60

    Re: Finding characters not in VBA character set

    Oh, gee, that's embarrassing. Thanks. I'll mark the thread solved.

+ 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] Macro not finding finding selected characters in strings
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-12-2017, 01:47 AM
  2. Repeat characters after certain character
    By jam_ran in forum Excel General
    Replies: 3
    Last Post: 01-23-2017, 07:02 PM
  3. Removing Characters from Right Until Certain Character
    By jam_ran in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2016, 12:19 PM
  4. Finding first character in a row of characters
    By barneyrubble1965 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2013, 10:45 AM
  5. [SOLVED] Formula for string of 9 characters to see if 8th character is odd or even
    By ste67@me.com in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2012, 11:40 AM
  6. Replies: 6
    Last Post: 10-29-2005, 09:05 PM
  7. Replies: 5
    Last Post: 08-29-2005, 11:05 PM

Tags for this Thread

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