Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim aSubs As Variant
Dim i As Long
Dim s As String
aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s")
Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count))
If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
s = c.Value
If Len(s) > 0 Then
For i = 0 To UBound(aSubs) Step 2
s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
Next i
c.Value = s
End If
Next c
Application.EnableEvents = True
End If
End Sub
Welcome to the MrExcel board!
If you want the result to be in the same cell that you type into then, no, a formula cannot do that. However, some vba could as described below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test. Note that I have coded this to only work in column B from row 2 downwards. I suggest you just test like that in a new worksheet to start with. Then if you cannot modify to suit your particular requirements, post back with more details.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, c As Range Dim aSubs As Variant Dim i As Long Dim s As String aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s") Set Changed = Intersect(Target, Columns("B"), Rows("2:" & Rows.Count)) If Not Changed Is Nothing Then Application.EnableEvents = False For Each c In Changed s = c.Value If Len(s) > 0 Then For i = 0 To UBound(aSubs) Step 2 s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1) Next i c.Value = s End If Next c Application.EnableEvents = True End If End Sub
You are welcome.Works perfect...Thank you!
If Not Changed Is Nothing Then
Application.EnableEvents = False
aSubs = Split("1 z 2 d 3 l 4 n 5 m 6 b 7 r 8 a 9 e 0 s")
For Each c In Changed
Sure, what column(s) to you want it applied to? Include the original column in your answer.any way to easily add this to one other column?
Sure, what column(s) to you want it applied to? Include the original column in your answer.
Is it the the same substitution(s) in the other column(s)?
The best method may depend on whether the columns are adjacent to each other, uniformly spaced apart etc, so the more accurately you can describe what you have, where and what you are trying to achieve, the better answer you are likely to get.
1. What are the substitutions for column M?i would love to use different substitutions Letters for the numbers in column p
1. What are the substitutions for column M?
2. What are the substitutions for column P?
3. Are we starting in row 2 for both columns? If not details please.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim aSubs As Variant
Dim i As Long
Dim s As String
'Column M
Set Changed = Intersect(Target, Columns("M"), Rows("2:" & Rows.Count))
If Not Changed Is Nothing Then
Application.EnableEvents = False
aSubs = Split("1 h 2 o 3 r 4 s 5 e 6 b 7 a 8 c 9 k 0 x")
For Each c In Changed
s = c.Value
If Len(s) > 0 Then
For i = 0 To UBound(aSubs) Step 2
s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
Next i
c.Value = s
End If
Next c
Application.EnableEvents = True
End If
'Column P
Set Changed = Intersect(Target, Columns("P"), Rows("2:" & Rows.Count))
If Not Changed Is Nothing Then
Application.EnableEvents = False
aSubs = Split("1 a 2 w 3 e 4 s 5 t 6 r 7 u 8 c 9 k 0 x")
For Each c In Changed
s = c.Value
If Len(s) > 0 Then
For i = 0 To UBound(aSubs) Step 2
s = Replace(s, aSubs(i), aSubs(i + 1), 1, -1, 1)
Next i
c.Value = s
End If
Next c
Application.EnableEvents = True
End If
End Sub