VBA code for changing capital letters become lower

Hi, I have some problems here. I have to change
1. the capital letter of "X" becomes lower letter "x" and
2. the "x" after first "x" becomes dot "."
Would you mind to help me?

For the example:
4391.4586X5.5.5/57x20/885.85x10/61x12/64.58x8/8328.2883x2x2x5/64.11.47x5/230.30.249.49x30/130.630.30.93.57.230x5

As you see the red one should be 4391.4586x5.5.5 and 8328.2883x2.2.5

For another example:
739.93.12x2/39.99x3/89.98x12/249.49.94.230.30x5/2989.8929X3X3X3/128x2.3/46x2/2165x5.5.5/0131.131.113.031.013x10/3

As you see the red one should be 2989.8929x3.3.3

I'm using Windows7 for notebook and XP for PC, Excel 2007
Any help would be great.





Regards


Jasa

Hi, thank you for your reply. If I added the code for function number 2, can I use this vba?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng             As Range
Dim rngC            As Range 'Single cell for loop
Dim arrstr()        As String
Dim strSplit        As String
Dim findPos         As Long

Dim strFinal        As String

Application.EnableEvents = False
Application.ScreenUpdating = False

Set rng = Intersect(Columns(1), Target)

If Not rng Is Nothing Then

    For Each rngC In Target.Cells
    
        If Not rngC.Value = "" Then
        
            arrstr = Split(rngC.Value, "/")
            
            Dim i As Long
            For i = LBound(arrstr) To UBound(arrstr)
            
                strSplit = arrstr(i)
                findPos = InStr(strSplit, "x")
                
                If findPos > 0 Then
                    strSplit = Left(strSplit, findPos - 1) & "x" & Replace(Right(strSplit, Len(strSplit) - findPos), "x", ".")
                End If
                
                strFinal = strFinal & strSplit & "/"
            
            Next i
            
            If Not strFinal = "" Then
                strFinal = Left(strFinal, Len(strFinal) - 1)
                rngC.Value = strFinal
            End If
            
        End If
        
    Next rngC
    
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thank you for helping me




Regards,


Jasa

as an option

Sub ertert()
Dim rng As Range, r As Range, s$, i&
Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With CreateObject("VBScript.RegExp")
    .Global = True: .IgnoreCase = True
    .Pattern = "X([0-9.]+X)+"
    For Each r In rng
        s = LCase(r.Value)
        With .Execute(s)
            For i = 0 To .Count - 1
                s = Mid(s, 1, .Item(i).firstindex + 1) & Replace(s, "x", ".", .Item(i).firstindex + 2, 2)
            Next
        End With
        r.Value = s
    Next r
End With
End Sub

Try this

Sub test()
    Dim r As Range, x, i As Long
    With CreateObject("vbscript.regexp")
        .IgnoreCase = True
        .Pattern = "(.*)(x)(.*)(\2)(.*)"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            x = Split(r.Value, "/")
            For i = 0 To UBound(x)
                Do While .test(x(i))
                    x(i) = .Replace(x(i), "$1x$3.$5")
                Loop
            Next
            r.Value = Join$(x, "/")
        Next
    End With
End Sub

Hi all, thank you so much for helping me

For John55
Hi, thanks a lot John. But, there's problem in this code, but I don't know what is it. I've tried to use this vba but it doesn't work.

For nilem
Hi, thank u so much for your help. It really works, thank u . But, would you mind if this code works for entire column, not just for column A? Thanks a lot nilem.

Sub ertert()
Dim rng As Range, r As Range, s$, i&
Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With CreateObject("VBScript.RegExp")
    .Global = True: .IgnoreCase = True
    .Pattern = "X([0-9.]+X)+"
    For Each r In rng
        s = LCase(r.Value)
        With .Execute(s)
            For i = 0 To .Count - 1
                s = Mid(s, 1, .Item(i).firstindex + 1) & Replace(s, "x", ".", .Item(i).firstindex + 2, 2)
            Next
        End With
        r.Value = s
    Next r
End With
End Sub

For jindon
Hi, thank u for helping me. It works in my worksheet. But, It seems this code works just for function number 2. The function for changing capital letter of "X" to "x" (lower) doesn't exist. Would you mind to add the first function in to this code? Thanks a lot.

Sub test()
    Dim r As Range, x, i As Long
    With CreateObject("vbscript.regexp")
        .IgnoreCase = True
        .Pattern = "(.*)(x)(.*)(\2)(.*)"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            x = Split(r.Value, "/")
            For i = 0 To UBound(x)
                Do While .test(x(i))
                    x(i) = .Replace(x(i), "$1x$3.$5")
                Loop
            Next
            r.Value = Join$(x, "/")
        Next
    End With
End Sub

Thank you for helping me




Regards,


Jasa

Originally Posted by jindon

If you want all "X" to be "x" then change

x = Split(r.Value, "/")

to

x = Split(LCasse$(r.Value), "/"))

Hi, thanks a lot Jindon. But, after changed the code, compile error & expected: end statement. So, I deleted one of ")" and it becomes

x = Split(LCasse$(r.Value), "/")

But, compile error again because of the sub or function of LCasse$ not defined. Would u mind to solve this problem?
Thank u for helping me jindon




Regards


Jasa

Oops, typo, single "s"
should read as

x = Split(LCase$(r.Value), "/"))

Originally Posted by jindon

Oops, typo, single "s"
should read as

x = Split(LCase$(r.Value), "/"))

Hi, thanks a lot Jindon. It totally works. But, I'm wondering this VBA can works for entire colums, 'cause sometimes the data is located in other columns beside A column. Would you mind to help me? Thank u so much Jindon.



Regards,


Jasa

You mean like this ?

Sub test()
    Dim rng As Range, r As Range, x, i As Long
    On Error Resume Next
    Set rng = Cells.SpecialCells(2)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    With CreateObject("vbscript.regexp")
        .IgnoreCase = True
        .Pattern = "(.*)(x)(.*)(\2)(.*)"
        For Each r In rng
            x = Split(LCase$(r.Value), "/")
            For i = 0 To UBound(x)
                Do While .test(x(i))
                    x(i) = .Replace(x(i), "$1x$3.$5")
                Loop
            Next
            r.Value = Join$(x, "/")
        Next
    End With
    Set rng = Nothing
End Sub

THANK YOU VERY MUCH FOR JINDON!!

It's totally great celebration for me.
Thanks a lot Jindon, you SOLVED my problem.
I think you're brilliant!
REALLY2X BRILLIANT!!

1073124_300.jpg

I really appreciate the efforts from John55, Nilem, and Jindon to help me.
It's really great and can't be explained by words.
I hope you don't mind to help me next time
Thank you guys for helping me, especially for Jindon!

Forgive me for all of my mistakes
and once again, I want to thank for all great efforts from Jindon.
Thank you so much, Jindon!
One word for Jindon, BRILLIANT!!


Regards,


Jasa

You are welcome.

Originally Posted by jindon

You are welcome.

Yeah, really a great celebration for this. Thanks a lot for helping me, John!!




Regards,


Jasa