Hi all, Looking for some assistance here from a knight in shining amour. I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Hi

not a knight in shining armour but this should help -

there is a function to do this in VBA

Sub StopHittingHead()
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:A159") 'change to suit
For Each cell In rng
cell.Value = StrConv(cell.Value, vbProperCase)
Next
End Sub

Cheers
JulieD

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

cheers :)


"SP" wrote in message
...
Julie, thanks your are my Damsel in Shinning Armour :)

Sheri
@--------

"JulieD" wrote in message
...
Hi

not a knight in shining armour but this should help -

there is a function to do this in VBA

Sub StopHittingHead()
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:A159") 'change to suit
For Each cell In rng
cell.Value = StrConv(cell.Value, vbProperCase)
Next
End Sub

Cheers
JulieD

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining
amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for
Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Julie, thanks your are my Damsel in Shinning Armour :)

Sheri
@--------

"JulieD" wrote in message
...
Hi

not a knight in shining armour but this should help -

there is a function to do this in VBA

Sub StopHittingHead()
Dim rng As Range
Dim cell As Range

Set rng = Range("A1:A159") 'change to suit
For Each cell In rng
cell.Value = StrConv(cell.Value, vbProperCase)
Next
End Sub

Cheers
JulieD

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Unless that is a very old article, the information isn't correct. The below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Tom, thanks for the information, much appreciated.

Sheri


"Tom Ogilvy" wrote in message
...
Unless that is a very old article, the information isn't correct. The
below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Strange reaction

You asked:
I need to modify it to do the whole spreadsheet.
I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

(I will admit, I interpreted Uppercase to mean all upper case. )

which my code does with no modification and is faster. Actually I don't
see where Julie's code changed anything from the original except to use
strconv - so either version (your original or Julies) would pretty much work
the same.

What was your actual purpose in posting? i.e. how did use of strconv change
anything?



--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Tom, thanks for the information, much appreciated.

Sheri


"Tom Ogilvy" wrote in message
...
Unless that is a very old article, the information isn't correct. The
below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining
amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for
Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Tom, This was my original posting:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.



This was straight from Microsoft's website and I didn't know how to specify
which range for it to change. I don't know anything about VBA, so what ever
I kept doing, it kept giving me error messages left and right. So I thought
I would just start from the beginning and not worry about putting the code
that I had butchered in my efforts to make it work for me.


"Tom Ogilvy" wrote in message
...
Strange reaction

You asked:
I need to modify it to do the whole spreadsheet.
I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.

(I will admit, I interpreted Uppercase to mean all upper case. )

which my code does with no modification and is faster. Actually I don't
see where Julie's code changed anything from the original except to use
strconv - so either version (your original or Julies) would pretty much
work
the same.

What was your actual purpose in posting? i.e. how did use of strconv
change
anything?



--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Tom, thanks for the information, much appreciated.

Sheri


"Tom Ogilvy" wrote in message
...
Unless that is a very old article, the information isn't correct. The
below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining
amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for
Applications.
' So, you must use the worksheet function in the following
form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia
and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

so the magic was the comment 'change to suit

Set rng = Range("A1:A159") 'change to suit

Well, thanks for responding - that clears up the mystery. Unfortunately, my
code didn't seem to require any changes :-(

--
Regards,
Tom Ogilvy



"SP" wrote in message
...
Tom, This was my original posting:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.



This was straight from Microsoft's website and I didn't know how to
specify
which range for it to change. I don't know anything about VBA, so what
ever
I kept doing, it kept giving me error messages left and right. So I
thought
I would just start from the beginning and not worry about putting the code
that I had butchered in my efforts to make it work for me.


"Tom Ogilvy" wrote in message
...
Strange reaction

You asked:
I need to modify it to do the whole spreadsheet.
I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

(I will admit, I interpreted Uppercase to mean all upper case. )

which my code does with no modification and is faster. Actually I
don't
see where Julie's code changed anything from the original except to use
strconv - so either version (your original or Julies) would pretty much
work
the same.

What was your actual purpose in posting? i.e. how did use of strconv
change
anything?



--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Tom, thanks for the information, much appreciated.

Sheri


"Tom Ogilvy" wrote in message
...
Unless that is a very old article, the information isn't correct.
The
below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in shining
amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for
Applications.
' So, you must use the worksheet function in the following
form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia
and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri

Tom, thanks - miss one little thing and the crazy code takes on a whole new
personality!!! One that I just assumed not work with :)


"Tom Ogilvy" wrote in message
...
so the magic was the comment 'change to suit

Set rng = Range("A1:A159") 'change to suit

Well, thanks for responding - that clears up the mystery. Unfortunately,
my
code didn't seem to require any changes :-(

--
Regards,
Tom Ogilvy



"SP" wrote in message
...
Tom, This was my original posting:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for Applications.
' So, you must use the worksheet function in the following form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA Phobia and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to reflect
capitalizing the first letter of each word in the cell.



This was straight from Microsoft's website and I didn't know how to
specify
which range for it to change. I don't know anything about VBA, so what
ever
I kept doing, it kept giving me error messages left and right. So I
thought
I would just start from the beginning and not worry about putting the
code
that I had butchered in my efforts to make it work for me.


"Tom Ogilvy" wrote in message
...
Strange reaction

You asked:
I need to modify it to do the whole spreadsheet.
I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

(I will admit, I interpreted Uppercase to mean all upper case. )

which my code does with no modification and is faster. Actually I
don't
see where Julie's code changed anything from the original except to
use
strconv - so either version (your original or Julies) would pretty
much
work
the same.

What was your actual purpose in posting? i.e. how did use of strconv
change
anything?



--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Tom, thanks for the information, much appreciated.

Sheri


"Tom Ogilvy" wrote in message
...
Unless that is a very old article, the information isn't correct.
The
below
should work.

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In
ActiveSheet.UsedRange.SpecialCells(xlConstants,xlT extValues)
if Ucase(x.Value) = x.Value then
x.Value = strconv(x,vbProperCase)
end if
Next
End Sub



to demo from the immediate window:

x = "ABCDE FGHI JKLM NOPQ"
? strconv(x,vbProperCase)
Abcde Fghi Jklm Nopq

Anyway, it worked for me.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"SP" wrote in message
...
Hi all, Looking for some assistance here from a knight in
shining
amour.
I
found this macro on Microsoft's KB:

Sub Proper_Case()
' Loop to cycle through each cell in the specified range.
For Each x In Range("C1:C5")
' There is not a Proper function in Visual Basic for
Applications.
' So, you must use the worksheet function in the following
form:
x.Value = Application.Proper(x.Value)
Next
End Sub


I need to modify it to do the whole spreadsheet. I am VBA
Phobia
and
have
been working with it for over 30 minutes. PLEASE HELP!!!

I just need to macro to change all cells that have uppercase to
reflect
capitalizing the first letter of each word in the cell.

Thanks So Much

@-------

Sheri