determine if first word of cell is capitalized
 

i am having issues with the code below:

Dim strCellAbove As String
Dim strCurrentCell As String
Dim s As String
Dim cell As Range

nlastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
- 1
myrow = Selection.Row
howmany = nlastrow - myrow

Set cell2 = Range(Selection, Selection.Offset(howmany, 0))
cell2.Select

For Each cell In Selection
If cell.Value = LCase(cell.Value) Then
cell.Offset(-1, 0).Value = cell.Offset(-1, 0).Value & " " &
cell.Value
ActiveSheet.Rows(cell.Row).Delete
End If
Next


Example:

JACK AND JILL
Jack and Jill
jack and Jill

i need the code to read the first word and determine if it starts with
a capital letter or not. currently it looks for all lowercase so
looking at the third example it sees this as uppercase because "Jill"
is capitalized. i need it to focus only on the first word of the text
string.

Anyone with any ideas?

God bless
jsd219

determine if first word of cell is capitalized
 

this may work for you. i assumed the data was in column A

Sub test()
Dim cell As Range
Dim rng As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & lastrow)

For Each cell In rng
If Asc(Left(cell.Text, 1)) = 65 And Asc(Left(cell.Text, 1)) <= 90 Then
MsgBox cell.Address & " " & Left(cell.Text, 1) & " is capitalized"
End If
Next
End Sub

--


Gary


"jsd219" wrote in message
ups.com...
i am having issues with the code below:

Dim strCellAbove As String
Dim strCurrentCell As String
Dim s As String
Dim cell As Range

nlastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
- 1
myrow = Selection.Row
howmany = nlastrow - myrow

Set cell2 = Range(Selection, Selection.Offset(howmany, 0))
cell2.Select

For Each cell In Selection
If cell.Value = LCase(cell.Value) Then
cell.Offset(-1, 0).Value = cell.Offset(-1, 0).Value & " " &
cell.Value
ActiveSheet.Rows(cell.Row).Delete
End If
Next


Example:

JACK AND JILL
Jack and Jill
jack and Jill

i need the code to read the first word and determine if it starts with
a capital letter or not. currently it looks for all lowercase so
looking at the third example it sees this as uppercase because "Jill"
is capitalized. i need it to focus only on the first word of the text
string.

Anyone with any ideas?

God bless
jsd219

determine if first word of cell is capitalized
 

Hi,

how about this one:

Public Function bCap(sTmp As String) As Boolean
Dim sChr As String
bCap = False
sChr = Left(sTmp, 1)
If UCase(sChr) = sChr Then bCap = True
End Function

You may have to add some error handling,
in case you have to deal with an empty string, too.

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

determine if first word of cell is capitalized
 

Thank you, but this one looked for Chr thus when it found a symbol in
the cell it caused problems.

I did take what you wrote and applied the same principle to my line and
it worked. Thank you for your help. ":-)

For Each cell In Selection
If Left(cell.Value, 1) = LCase(Left(cell.Value, 1)) Then
cell.Offset(-1, 0).Value = cell.Offset(-1, 0).Value & " " &
cell.Value
ActiveSheet.Rows(cell.Row).Delete
End If

God bless
jsd219

Gary Keramidas wrote:
this may work for you. i assumed the data was in column A

Sub test()
Dim cell As Range
Dim rng As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & lastrow)

For Each cell In rng
If Asc(Left(cell.Text, 1)) = 65 And Asc(Left(cell.Text, 1)) <= 90 Then
MsgBox cell.Address & " " & Left(cell.Text, 1) & " is capitalized"
End If
Next
End Sub

--


Gary


"jsd219" wrote in message
ups.com...
i am having issues with the code below:

Dim strCellAbove As String
Dim strCurrentCell As String
Dim s As String
Dim cell As Range

nlastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row
- 1
myrow = Selection.Row
howmany = nlastrow - myrow

Set cell2 = Range(Selection, Selection.Offset(howmany, 0))
cell2.Select

For Each cell In Selection
If cell.Value = LCase(cell.Value) Then
cell.Offset(-1, 0).Value = cell.Offset(-1, 0).Value & " " &
cell.Value
ActiveSheet.Rows(cell.Row).Delete
End If
Next


Example:

JACK AND JILL
Jack and Jill
jack and Jill

i need the code to read the first word and determine if it starts with
a capital letter or not. currently it looks for all lowercase so
looking at the third example it sees this as uppercase because "Jill"
is capitalized. i need it to focus only on the first word of the text
string.

Anyone with any ideas?

God bless
jsd219

determine if first word of cell is capitalized
 

Thank you, :-)

God bless
jsd219


Helmut Weber wrote:
Hi,

how about this one:

Public Function bCap(sTmp As String) As Boolean
Dim sChr As String
bCap = False
sChr = Left(sTmp, 1)
If UCase(sChr) = sChr Then bCap = True
End Function

You may have to add some error handling,
in case you have to deal with an empty string, too.

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"