??? 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.
RESULTCode: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
A1 B2 C3 D4 E5 F6
31 2 r3 54 65 h6 t7 g8
a1 h2 g3 y4 5 j6 k7 8 l9
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
No.. Something like this
Public Function
Case "":
m = m + 0
Case "A":
m = A1
Case "B":
m = B1
Case "C":
m = C1
hi.. yes Always Letters
??? 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.
RESULTCode: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
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?Can you describe the requirement with a little more detail?
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks for your reply
Anything can be written In contrast to the A
A to "Smith"
B to "Korea"
C to "Linda"
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.Anything can be written In contrast to the A
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
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
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
can be written asCode:Case "": m = m + 0 Case "A": m = A1 Case "B": m = B1 Case "C": m = C1
Your last example could beCode:SELECT CASE myVariable Case "" 'do nothing Case "A","B","C","D"... myVariable = myVariable & "1" END SELECT
There's no need there to pass a variable (arg1) to another variable (strResult).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
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.
something wrong
type A or B or C or D
CONVERT.zip
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
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
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.
I'm sorry (moke123)
A word as the sample please