code needed for lower case and upper case with condition..

[VBABEGINER]

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..

'*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..

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..

 

[VBABEGINER]

Anyone please help in this..

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..

'*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..

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..

 

[CalcSux78]

This method uses a function to split a cells value into an array and compare each element. You will need to alter the sTemp line where punctuation is being temporarily removed from the string to account for any possible non-alpha characters.

Option Explicit
Option Compare Text

Sub EvalData()
Dim cel As Range
Dim rng As Range

With Worksheets("Sheet1")
    Set rng = .Cells(.Rows.Count, 1).End(xlUp)
    For Each cel In .Range(.[A3], rng)
        cel = ConditionalCase(cel.Value)
    Next cel
End With
End Sub

Function ConditionalCase(ByVal str As String) As String
Dim vStr As Variant
Dim i%
Dim sTemp$


vStr = Split(str, " ")
For i = LBound(vStr) To UBound(vStr)
    sTemp = vStr(i)
    'remove punctuation from string segment
    sTemp = Replace(Replace(Replace(Replace(Replace(Replace(sTemp, ",", ""), _
    ".", ""), "(", ""), ")", ""), "?", ""), "!", "")
    Select Case sTemp
        Case "and", "is", "for", "of", "or"
            'lower case
            vStr(i) = LCase(vStr(i))
        Case "TRIA", "TRIPRA", "OFAC", "PPACA", "EBL", "ERISA"
            vStr(i) = UCase(vStr(i))
        Case Else
            vStr(i) = StrConv(vStr(i), vbProperCase)
    End Select
Next i
ConditionalCase = Join(vStr, " ")
End Function

 

[VBABEGINER]

Really Thankfull to you..You saved me..Friday will be my last day to submit this...and i was real panic, not understand how to do this...
Thank You Thank You so much...for your great help..
Exactly work for me..Great code..

Pls also revert me in future..whenever possible..Thanks..vbabeginer

This method uses a function to split a cells value into an array and compare each element. You will need to alter the sTemp line where punctuation is being temporarily removed from the string to account for any possible non-alpha characters.

Option Explicit
Option Compare Text

Sub EvalData()
Dim cel As Range
Dim rng As Range

With Worksheets("Sheet1")
    Set rng = .Cells(.Rows.Count, 1).End(xlUp)
    For Each cel In .Range(.[A3], rng)
        cel = ConditionalCase(cel.Value)
    Next cel
End With
End Sub

Function ConditionalCase(ByVal str As String) As String
Dim vStr As Variant
Dim i%
Dim sTemp$


vStr = Split(str, " ")
For i = LBound(vStr) To UBound(vStr)
    sTemp = vStr(i)
    'remove punctuation from string segment
    sTemp = Replace(Replace(Replace(Replace(Replace(Replace(sTemp, ",", ""), _
    ".", ""), "(", ""), ")", ""), "?", ""), "!", "")
    Select Case sTemp
        Case "and", "is", "for", "of", "or"
            'lower case
            vStr(i) = LCase(vStr(i))
        Case "TRIA", "TRIPRA", "OFAC", "PPACA", "EBL", "ERISA"
            vStr(i) = UCase(vStr(i))
        Case Else
            vStr(i) = StrConv(vStr(i), vbProperCase)
    End Select
Next i
ConditionalCase = Join(vStr, " ")
End Function[/QUOTE]