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

V

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,232
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..
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..
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Anyone please help in this..

VBABEGINER said:
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..
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..
Click to expand...
 
Upvote 0
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.

Code: 
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
 
Last edited:
Upvote 0
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

CalcSux78 said:
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.

Code: 
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]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
279
ItalianPlatinum
I
T
  • Question
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
0
Views
198
ttp921
T
JohnZ1156
  • Solved
Upper Case VBA Script converting formulas to values
Replies
9
Views
605
JohnZ1156
JohnZ1156
S
  • Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
663
NdNoviceHlp
NdNoviceHlp
T
  • Solved
VBA generates word docs, request to amend code to include description of deadlines/tasks due
Replies
4
Views
349
ttp921
T

Forum statistics

Threads
1,215,417
Messages
6,124,783
Members
449,188
Latest member
Hoffk036

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top