Results 1 to 3 of 3

Find and replace-Replacement is always capital leter

Threaded View

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    BG
    MS-Off Ver
    Excel 2003
    Posts
    21

    Find and replace-Replacement is always capital leter

    Hi Guys,

    I'm trying to write VBA code which find Cyrillic letters within range, to change their color and replace them with Latin letters.
    For example A,a,В,X,x exists also in Cyrillic alphabet.

    My question is why the replacement are always with capital letter?
    I've tried for example for a Replacement:=Chr(141) and Replacement:="a" but again it returns always A.


    Sub HLCyrillic()
    Dim ArrCyrCells() As String
    ReDim ArrCyrCells(0)
    Application.ScreenUpdating = False
        Worksheets("Sheet1").Activate
        Range("b1:b10").Select
        s = 0
        For Each c In Selection
            lght = Len(c)
            h = s
                For x = 1 To lght
                   cd = c.Value
                   r = Asc(Mid(cd, x, 1))
                   With c
                   If r = "63" Then
                      .Characters(Start:=x, Length:=1).Font.Color = vbRed
                      s = s + 1
                    Else
                      .Characters(Start:=x, Length:=1).Font.Color = vbBlack
                    End If
                    End With
                 Next x
                    If s > h Then
          ReDim Preserve ArrCyrCells(UBound(ArrCyrCells) + 1)
          ArrCyrCells(UBound(ArrCyrCells)) = c.Address
        End If
        Next c
        Application.ScreenUpdating = True
    If s = 0 Then
      Msg = "There are not any cyrillic leters within the range"
      MsgBox Msg
    Else
      Msg = "There are " & s & " cyrillic leters within the range." & vbNewLine & _
            "Do you want to replace them automaticaly?"
      Ans = MsgBox(Msg, vbYesNo)
        If Ans = vbNo Then
            Exit Sub
        Else
            For i = 1 To UBound(ArrCyrCells)
            Set CyrCell = Range(ArrCyrCells(i))
                CyrCell.Replace What:=ChrW(1040), Replacement:="A", LookAt:=xlPart 'A
                CyrCell.Replace What:=ChrW(1072), Replacement:=Chr(141), LookAt:=xlPart 'a
                CyrCell.Replace What:=ChrW(1042), Replacement:="B", LookAt:=xlPart 'B
                CyrCell.Replace What:=ChrW(1074), Replacement:="b", LookAt:=xlPart 'b
                CyrCell.Replace What:=ChrW(1045), Replacement:="E", LookAt:=xlPart 'E
                CyrCell.Replace What:=ChrW(1077), Replacement:="e", LookAt:=xlPart 'e
                CyrCell.Replace What:=ChrW(1047), Replacement:="3", LookAt:=xlPart '3
                CyrCell.Replace What:=ChrW(1050), Replacement:="K", LookAt:=xlPart 'K
                CyrCell.Replace What:=ChrW(1082), Replacement:="k", LookAt:=xlPart 'k
                CyrCell.Replace What:=ChrW(1052), Replacement:="M", LookAt:=xlPart 'M
                CyrCell.Replace What:=ChrW(1084), Replacement:="m", LookAt:=xlPart 'm
                CyrCell.Replace What:=ChrW(1053), Replacement:="H", LookAt:=xlPart 'H
                CyrCell.Replace What:=ChrW(1085), Replacement:="h", LookAt:=xlPart 'h
                CyrCell.Replace What:=ChrW(1054), Replacement:="O", LookAt:=xlPart 'O
                CyrCell.Replace What:=ChrW(1086), Replacement:="o", LookAt:=xlPart 'o
                CyrCell.Replace What:=ChrW(1056), Replacement:="P", LookAt:=xlPart 'P
                CyrCell.Replace What:=ChrW(1088), Replacement:="p", LookAt:=xlPart 'p
                CyrCell.Replace What:=ChrW(1057), Replacement:="C", LookAt:=xlPart 'C
                CyrCell.Replace What:=ChrW(1089), Replacement:="c", LookAt:=xlPart 'c
                CyrCell.Replace What:=ChrW(1058), Replacement:="T", LookAt:=xlPart 'T
                CyrCell.Replace What:=ChrW(1090), Replacement:="t", LookAt:=xlPart 't
                CyrCell.Replace What:=ChrW(1059), Replacement:="Y", LookAt:=xlPart 'Y
                CyrCell.Replace What:=ChrW(1091), Replacement:="y", LookAt:=xlPart 'y
                CyrCell.Replace What:=ChrW(1061), Replacement:="X", LookAt:=xlPart 'X
                CyrCell.Replace What:=ChrW(1093), Replacement:="x", LookAt:=xlPart 'x
            Next
        End If
    End If
    End Sub
    Thank you!

    Regards,
    Guerolito
    Last edited by Guerolito; 08-22-2013 at 01:41 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. condense replace / replacement formulas
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2013, 07:02 PM
  2. Replies: 4
    Last Post: 04-27-2012, 06:38 AM
  3. find same word in all capital letters
    By sportingsports in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-13-2010, 08:43 AM
  4. Mid Find First Capital Letter
    By pauldaddyadams in forum Excel General
    Replies: 6
    Last Post: 11-01-2009, 05:13 PM
  5. [SOLVED] How to tell if Replace() did a replacement?
    By tjw@fadavis.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-04-2006, 09:50 AM

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