Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49

    Module for Convert Letter

    Hello Everyone.


    such as the sample I needed a module to convert letters

    Thank you for your help

    Click image for larger version. 

Name:	555.png 
Views:	61 
Size:	19.9 KB 
ID:	47692

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??? Always Letters?? No numbers or special chars (&*^%$#@ etc)??
    Can you describe the requirement with a little more detail?

    This may give you a starting point. You'll need a function if you want to return a value.

    Code:
    Sub addchar()
        Dim s(2)  As String, out As String
        s(0) = "ABCDEF"
        s(1) = "3 r56htg"
        s(2) = "ahgy jk l"
    
        Dim I As Integer, j As Integer
        For I = 0 To 2
            For j = 1 To Len(s(I))
            out = out & Mid(s(I), j, 1) & j & " "
            Next
            Debug.Print out
            out = ""
        Next
    End Sub
    RESULT

    A1 B2 C3 D4 E5 F6
    31 2 r3 54 65 h6 t7 g8
    a1 h2 g3 y4 5 j6 k7 8 l9

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    What happens if it is CAB ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    No.. Something like this

    Public Function

    Case "":
    m = m + 0
    Case "A":
    m = A1
    Case "B":
    m = B1
    Case "C":
    m = C1

  5. #5
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    hi.. yes Always Letters

    Quote Originally Posted by orange View Post
    ??? Always Letters?? No numbers or special chars (&*^%$#@ etc)??
    Can you describe the requirement with a little more detail?

    This may give you a starting point. You'll need a function if you want to return a value.

    Code:
    Sub addchar()
        Dim s(2)  As String, out As String
        s(0) = "ABCDEF"
        s(1) = "3 r56htg"
        s(2) = "ahgy jk l"
    
        Dim I As Integer, j As Integer
        For I = 0 To 2
            For j = 1 To Len(s(I))
            out = out & Mid(s(I), j, 1) & j & " "
            Next
            Debug.Print out
            out = ""
        Next
    End Sub
    RESULT

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can you describe the requirement with a little more detail?
    Agreed. Examples of output only don't really help to focus answers. Is this related to position in a string (where A is first, it's A1) or is it alphabet related (e.g. O would be O15)? If so, then what is o versus O?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    Thanks for your reply

    Anything can be written In contrast to the A

    A to "Smith"
    B to "Korea"
    C to "Linda"

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Anything can be written In contrast to the A
    So this is an Excel issue because A1, B1, C1 are cell references which could contain anything? Or they are Access variables? If you have code you're using that doesn't do what you want, maybe post it and state what the issue is. You're being too vague.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    Code:
    Public Function fcnConvert(arg1 As String) As String
        Dim strResult As String
        strResult = arg1
        
        strResult = Replace([strResult], "A", "txt1")
        strResult = Replace([strResult], "B", "txt2")
        strResult = Replace([strResult], "C", "txt3")
    
    
        
        fcnConvert = strResult
    End Function

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That function will replace strResult (e.g. "A1567BC") with "txt1" when it finds "A" then wipe that out by replacing it with "txt2" when it finds "B" in "A1567BC", then wipe that out by replacing it with "txt3" when it finds "C" in "A1567BC". If that cannot happen then you're OK. This
    Code:
    Case "":
    m = m + 0
    Case "A":
    m = A1
    Case "B":
    m = B1
    Case "C":
    m = C1
    can be written as
    Code:
    SELECT CASE myVariable
      Case ""
      'do nothing
      Case "A","B","C","D"...
       myVariable = myVariable & "1"
    END SELECT
    Your last example could be
    Code:
    Public Function fcnConvert(arg1 As String) As String
    
    arg1 = Replace([arg1], "A", "txt1")
    arg1 = Replace([arg1], "B", "txt2")
    arg1 = Replace([arg1], "C", "txt3")
    fcnConvert = arg1
    
    End Function
    There's no need there to pass a variable (arg1) to another variable (strResult).
    Last edited by Micron; 04-23-2022 at 09:14 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    something wrong

    type A or B or C or D

    CONVERT.zip

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,857
    Nothing wrong, code is behaving exactly as Micron said it would in post #10.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    It would really help to explain what it is your hoping to do using real world examples.
    What are you passing into the function (ie. A letter?, A word?, A Name?)
    What should the function do to what you pass in. Step by step.
    Whats the purpose of this exercise?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks moke123, I thought it was just me. What exactly is the requirement? zozzz, please give a description and meaningful samples (before and after).
    Apparently I guessed wrong in post #2. Micron may be a better guesser, but if you want an answer or approach to solving an issue, then please communicate the issue is simple, clear terms and preferably with examples of inputs and desired outputs.

  15. #15
    zozzz is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    49
    I'm sorry (moke123)
    A word as the sample please


Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-22-2020, 01:15 PM
  2. Replies: 4
    Last Post: 09-18-2012, 05:07 AM
  3. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  4. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  5. Replies: 2
    Last Post: 08-03-2010, 02:47 PM

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