Initial capital customized

[PcMax]

Hi,


The combinations of the items requiring correction, unfortunately I have not managed to solve all situations
I attach the file with new entries

[snb]

Sub M_snb()
   sn = [B3:B12]
   
   For j = 1 To UBound(sn)
     sn(j, 1) = Replace(Replace(StrConv(Replace(Replace(sn(j, 1), ".", ". "), "/", "/ "), 3), ". ", "."), "/ ", "/")
     
     st = Split(sn(j, 1))
     For jj = 0 To UBound(st)
         If st(jj) Like "*[0-9]*" Then st(jj) = UCase(st(jj))
     Next
     sn(j, 1) = Join(st)
     
     For Each it In Split("di da con per mm in a e la le")
       If InStr(1, sn(j, 1) & " ", " " & it & " ", 1) Then sn(j, 1) = Trim(Replace(Replace(sn(j, 1) & " ", " " & UCase(it) & " ", " " & it & " "), " " & StrConv(it, 3) & " ", " " & it & " "))
     Next
   Next
   
   [G3:G12] = sn
End Sub

[Rick Rothstein]

Here is another macro that you can try...

Sub SpecialProperCase()
  Dim R As Long, X As Long, CellVals As Variant, Words() As String
  CellVals = Range("B3", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(CellVals)
    Words = Split(Application.Proper(CellVals(R, 1)))
    For X = 0 To UBound(Words)
      If Words(X) Like "#*" Then
        Words(X) = UCase(Words(X))
      ElseIf " di da con per mm in a e la le " Like "*[!A-Z0-9]" & LCase(Words(X)) & "[!A-Z0-9]*" Then
        Words(X) = LCase(Words(X))
      End If
    Next
    CellVals(R, 1) = Join(Words)
  Next
  Range("G3:G" & UBound(CellVals)) = CellVals
End Sub

Note: If you find there are other stand-alone words that you want to appear all lower case, add them to the space delimited list shown in blue above (but make sure the text starts and ends with a space after you have done so).

[PcMax]

Hi,

snb, I tested the code in the loop Vb6 and work perfectly

Sub M_snb()
sn = [B3:B12]

For j = 1 To UBound(sn)
sn(j, 1) = Replace(Replace(StrConv(Replace(Replace(sn(j, 1), ".", ". "), "/", "/ "), 3), ". ", "."), "/ ", "/")

st = Split(sn(j, 1))
For jj = 0 To UBound(st)
If st(jj) Like "*[0-9]*" Then st(jj) = UCase(st(jj))
Next
sn(j, 1) = Join(st)

For Each it In Split("di da con per mm in a e la le")
If InStr(1, sn(j, 1) & " ", " " & it & " ", 1) Then sn(j, 1) = Trim(Replace(Replace(sn(j, 1) & " ", " " & UCase(it) & " ", " " & it & " "), " " & StrConv(it, 3) & " ", " " & it & " "))
Next
Next

[G3:G12] = sn
End Sub

Rick Rothstein
I tried the code with Excel, perfect
note: Not available in Vb6 code: Application.Proper

Thank you all for the suggestions received

[Rick Rothstein]

Rick Rothstein
I tried the code with Excel, perfect
note: Not available in Vb6 code: Application.Proper

Are you using the VB6 inside of Excel or the compiled version of Visual Basic 6? If you are using the VB6 inside of Excel, what version of Excel are you using? Just so you know, I tested the code before I posted it and what I posted works for me in my copies of XL2003, XL2007 and XL2010 and I can see no reason why it would not work in XL2013 and XL2016 (when released).

If you still have trouble with the code, try replacing Application.Proper with WorksheetFunction.Proper and see if that solves your problem.

Edit Note: Do you have a procedure or function or macro named Proper? If you do, there is a chance using that name is interfering with the attempted use of Proper when called from the Application object.

[PcMax]

Hi,

Application.Proper with WorksheetFunction.Proper

I used XL2013 and the Vba code you have proposed is correct.

I can not use the sequence in the Old program Vb6.
In previous responses I had reported the problem

[Rick Rothstein]

I can not use the sequence in the Old program Vb6.
In previous responses I had reported the problem

If by that you mean the old stand-alone, compiled version of VB6, pretty much nothing offered in an Excel forum will work on it. While VB6 and VBA (what's built into Excel, as well as other Office products) share a large, common core, there are huge differences unique to each which make sharing code back-and-forth between them unlikely to work. VB6 has a richer set of ActiveX controls and its Form is constructed differently from Excel's UserForm... in addition to that, VBA has been hughly extended with the Excel object model which brings hundreds of "extras" to the language that are not duplicated in VB6 at all. In order to move code back-and-forth between the two programming worlds, you need a high degree of familiarity with both so you can recognize when one's code snippet won't work in the other's so that you can write equivalent code to make up the difference.

[PcMax]

Hi,

If by that you mean the old stand-alone, compiled version of VB6, pretty much nothing offered in an Excel forum will work on it.

I confirm that I had to adapt the code to work Vb6

Thank you very much