VBABEGINER
Well-known Member
- Joined
- Jun 15, 2011
- Messages
- 1,284
- Office Version
- 365
- Platform
- Windows
I have question on this, please.
Please guide, what can be the solution..
I have some preposition's in my sentence..which are..
and, at, is, for, of, or
I want to keep them in small letter and rest other text in proper case...
I am showing my code..
***As well as.....
I have some text with me, which I want them into in UPPER case only and rest other text in sentence in Proper case.
The words are..
TRIA, TRIPRA, OFAC, PPACA, EBL, ERISA
and code for this..
but it making entire cell in lower & upper...
can any please help in code this..in correct format..
Please guide, what can be the solution..
I have some preposition's in my sentence..which are..
and, at, is, for, of, or
I want to keep them in small letter and rest other text in proper case...
I am showing my code..
Code:
'*Text Formating Proper, LowerCase, UpperCase
With Worksheets("Sheet1")
RowCnt2 = .Range("A3").End(xlDown).Row
For j = 3 To RowCnt2
.Cells(j, "A").Value = WorksheetFunction.Proper(.Cells(j, "A").Value)
Next j
End With
With Worksheets("Sheet1")
RowCnt3 = .Range("A3").End(xlDown).Row
For l = 3 To RowCnt3
If Range("A" & l).Value Like "* and *" Or Range("A" & l).Value Like "* at *" Or Range("A" & l).Value Like "* is *" Or Range("A" & l).Value Like "* for *" Or Range("A" & l).Value Like "* of *" Or Range("A" & l).Value Like "* or *" Then
'If InStr(Cells(i, 9).Value, "LK") Then
.Cells(l, "A").Value = LCase(.Cells(l, "A").Value)
End If
Next l
End With
***As well as.....
I have some text with me, which I want them into in UPPER case only and rest other text in sentence in Proper case.
The words are..
TRIA, TRIPRA, OFAC, PPACA, EBL, ERISA
and code for this..
Code:
With Worksheets("Sheet1")
RowCnt4 = .Range("A3").End(xlDown).Row
For m = 3 To RowCnt4
If Range("A" & m).Value Like "* tria *" Or Range("A" & m).Value Like "* tripra *" Or Range("A" & m).Value Like "* ofaca *" Or Range("A" & m).Value Like "* ppaca *" Or Range("A" & m).Value Like "* ebl *" Or Range("A" & m).Value Like "* erisa *" Or _
Range("A" & m).Value Like "* tria" Or Range("A" & m).Value Like "* tripra" Or Range("A" & m).Value Like "* ofaca" Or Range("A" & m).Value Like "* ppaca" Or Range("A" & m).Value Like "* ebl" Or Range("A" & m).Value Like "* erisa" Or _
Range("A" & m).Value Like "tria *" Or Range("A" & m).Value Like "tripra *" Or Range("A" & m).Value Like "ofaca *" Or Range("A" & m).Value Like "ppaca *" Or Range("A" & m).Value Like "ebl *" Or Range("A" & m).Value Like "erisa *" Then
'If InStr(Cells(i, 9).Value, "LK") Then
.Cells(m, "A").Value = UCase(.Cells(m, "A").Value)
End If
Next m
End With
'*Text Formating Proper, LowerCase, UpperCase
but it making entire cell in lower & upper...
can any please help in code this..in correct format..