I am working with a very large sheet that lists addresses. However the state
code is listed with one upper and one lowercase letter. I want to design a
macro to replace only the last letter with its uppercase equivelant.

eg

Santa Ana Ca
to
Santa Ana CA

Without knowing what range you are trying to deal with I can offer
little help but here is the function you want:

Range("A1").Value = Left(Range("A1").Value,Len(Range("A1").Value -1 _
& WorksheetFunction.Upper(Right(Range("A1").value,1) )

This will do what you asked with range A1 post back if you need more
help

HTH

Die_Another_Day

ALoecher wrote:
I am working with a very large sheet that lists addresses. However the state
code is listed with one upper and one lowercase letter. I want to design a
macro to replace only the last letter with its uppercase equivelant.

eg

Santa Ana Ca
to
Santa Ana CA

Dim Cell As Range

For Each Cell In Range("A:A")
If Cell < "" Then Cell = Left(Cell, Len(Cell) - 1) & UCase(Right(Cell, 1))
Next Cell

(Adjust your range accordingly)


"ALoecher" wrote:

I am working with a very large sheet that lists addresses. However the state
code is listed with one upper and one lowercase letter. I want to design a
macro to replace only the last letter with its uppercase equivelant.

eg

Santa Ana Ca
to
Santa Ana CA

If only one of 2 letters is lower case, and you want the lower case letter
made upper case, then just make both letters upper case. HTH Otto
For Each Cell In Range("A:A")
If Cell < "" Then Cell.Value = UCase(Cell.Value)
Next Cell

"ALoecher" wrote in message
...
I am working with a very large sheet that lists addresses. However the
state
code is listed with one upper and one lowercase letter. I want to design a
macro to replace only the last letter with its uppercase equivelant.

eg

Santa Ana Ca
to
Santa Ana CA

You did ask for a macro and got one solution. Here is a formula solution
FWIW.

=LEFT(A1,LEN(A1)-1)&PROPER(RIGHT(A1,1))

HTH
Regards,
Howard

"ALoecher" wrote in message
...
I am working with a very large sheet that lists addresses. However the
state
code is listed with one upper and one lowercase letter. I want to design a
macro to replace only the last letter with its uppercase equivelant.

eg

Santa Ana Ca
to
Santa Ana CA