Results 1 to 10 of 10
  1. #1
    perus josh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2018
    Posts
    3

    Find and Replace in macro

    Hi


    i have vb code for excel to find and replace, any one can help me to work on access for the same.

    Sub RemoveSplChar()
    Dim rCell As Range


    For Each rCell In ActiveSheet.Columns("A:A").SpecialCells(xlCellType Constants, xlTextValues).Cells
    With rCell
    .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
    .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
    .Value = Application.WorksheetFunction.Substitute(.Value, "å", "a")
    .Value = Application.WorksheetFunction.Substitute(.Value, "Å", "A")
    .Value = Application.WorksheetFunction.Substitute(.Value, "ð", "D")
    .Value = Application.WorksheetFunction.Substitute(.Value, "Ð", "D")
    .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
    .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
    .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
    .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
    .Value = Application.WorksheetFunction.Substitute(.Value, "Ó", "O")
    .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "o")
    .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
    .Value = Application.WorksheetFunction.Substitute(.Value, "Ý", "Y")
    .Value = Application.WorksheetFunction.Substitute(.Value, "ý", "y")


    End With

    Next rCell


    End Sub

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us what you are doing in Access vba.
    You will want the Replace() function.

  3. #3
    perus josh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2018
    Posts
    3
    Hi Orange,

    these
    special character or Word have to replace
    Árrow
    Ráw
    Håtch

    The result should be as below

    Arrow
    Raw
    Hatch

    thank you

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @Josh,

    Here is a sample using your test data. I've used Split and Join to do the substitution.

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : testJosh
    ' Author    : Jack
    ' Date      : 27-Mar-18
    ' Purpose   : Routine to convert/replace some ascii characters with others.
    '---------------------------------------------------------------------------------------
    ' NOTES: The question is from https://www.accessforums.net/showthread.php?t=71318
    'This routine uses the Arraychange to contain chars to change and
    ' arrayKeepIt to contin the replacements desired. The offsets/positions in these 2 arrays are sync'd
    'when testing the sample data, thecode looks for the character to change and splits the test data.
    'It then takes the replacement character and joins it with the remainder of the test data.
    'In this sample the test string with the substituted character is  printed to the immediate window.
    '
    ' I used vbCompareBinary on the Split to ensure case sensitivity.
    
    Sub testJosh()
              Dim arrayChange As Variant, arrayKeepIt As Variant
    10       On Error GoTo testJosh_Error
    
    20        arrayChange = Array("Á", "á", "å", "ð", "Ð", "É", "é", "í", "Í", "Ó", "ó", "ú", "Ý", "ý")
    30        arrayKeepIt = Array("A", "a", "a", "d", "D", "E", "e", "i", "I", "O", "o", "u", "Y", "y")
               
              Dim i As Integer
              Dim a(2) As String    'Test values
    
    40        a(0) = "Árrow"
    50        a(1) = "Ráw"
    60        a(2) = "Håtch"
    
              Dim strSplit() As String
              Dim j As Integer
    
    70        For j = LBound(a) To UBound(a)
    80            If Nz(a(j), vbNullString) <> vbNullString Then
    90                For i = 0 To UBound(arrayChange)
    100                   strSplit = Split(a(j), arrayChange(i), , vbBinaryCompare) 'check case equality
    110                   If UBound(strSplit) > 0 Then  'there maybe more chars to check
    120                       Debug.Print Join(strSplit, arrayKeepIt(i))
    130                   End If
    140               Next i
    150           End If
    
    160       Next j
    
    170      On Error GoTo 0
    testJosh_Exit:
    180      Exit Sub
    
    testJosh_Error:
    
    190       MsgBox "Error " & Err.Number & " on line " & Erl & " (" & Err.Description & ") in procedure testJosh of Module Module1"
    200       GoTo testJosh_Exit
    
    End Sub
    Results of the test data:

    Arrow
    Raw
    Hatch
    Last edited by orange; 03-27-2018 at 12:31 PM.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    But are those always going to be the characters that need to be removed? I was thinking of just testing if each character in a string lies between say Ascii 65 and 90 or 97 to 122, but that might not be a valid approach.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Micron,

    I agree. I have used this code(below) from mvps or an adaptation of it for many years.
    I was thinking of Replace (wordInvolved,chrToRemove,chrToSubstitute), but as I started to test, I hit on Split and Join??

    http://access.mvps.org/access/strings/str0002.htm

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I get it. You just wanted to beat me to an answer 'cause you know I'm on line
    As you know, sometimes the description of the problem, or the data that we think is relevant, couldn't be farther from the real thing. So I was just ruminating.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    perus josh is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2018
    Posts
    3
    hi orange,
    thank you,

    if i need to replace the word like "Avenue" to "Ave"
    can be done through the above code.

    and

    guide me how to execute the above code.

    thank you.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have a number of words to replace/substitute with another word/string I would suggest the following approach--but there may be many others.

    Create a table with 2 fields.
    field1 to contain the to be replaced string eg Avenue
    field2 to contain the preferred word/string eg Ave

    You might have "Ave." to be replaced with Ave

    I had a similar situation several years ago to standardize some Address terms, and another to standardize "Position names" of Contacts. The idea is to parse the "address" values and make a replacement. The basic logic is:
    read a record; parse the Address field value, if you find a word/term from the address that matches a value in field2 of your table, then replace the original term with the preferred term, update the record, and repeat until end of file.

    The code I provided earlier was for single character substitution. I'm sure you could adapt it, but I'd go with the table and fields approach. I would consider bringing the terms and substitutes into an array for processing, but that depends on how many terms/substitutes you have.

    If you are working with addresses, you might look to the Post Office for addressing rules and accepted abbreviations. See this for an example

    Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find and Replace in macro code
    By chris.emery in forum Macros
    Replies: 1
    Last Post: 10-11-2015, 08:12 AM
  2. Code/Macro for Find and Replace with Checkbox
    By mortonsafari in forum Access
    Replies: 3
    Last Post: 07-21-2015, 06:51 PM
  3. Find and Replace in Macro and VB
    By Baldeagle in forum Macros
    Replies: 4
    Last Post: 01-28-2014, 10:32 AM
  4. Find and Replace Macro
    By TrevorM in forum Macros
    Replies: 11
    Last Post: 12-28-2013, 06:11 PM
  5. Find and Replace Within a Macro
    By bmschaeffer in forum Access
    Replies: 1
    Last Post: 12-20-2011, 11:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums