Solved: CHANGE FROM ALL UPPER CASE

[GaryB]

Hi,

I have a database that is to be used for a mailing list and it is all in uppercase. I need to do a global change to Upper and lower case. Anyone have an idea on how I can do this.

Thanks

Gary

[Bob Phillips]

[vba]

For Eachcell In Activesheet.UsedRange
cell.Value = UCase(cell.Value)
Next cell
[/vba]

[Norie]

Gary

You don't actually say how you want to change the case but I think you might want to take a look at StrConv with the vbProperCase argument.

[GaryB]

My aplologies, I wasn't clear enough. I do want to change Proper Case but don't know enough VB to do it myself. I have a list of 800 names and address that are all capitalized and I need to change it to just the first letter of the word capitalized. I do have a way of doing this by importing it into Indesign as a comma, text delimited file, making the change there and importing it back into Excel. Which I have done. I was looking for a way to do it in Excel and eliminate about 4 steps to the process.

Thanks,

Gary

[RonMcK]

Gary,

Combining XID's and Norie's suggestions gives

[VBA]For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell [/VBA]

Works very nicely on my very small sample. Areas that may be problems: hyphenated names (StrConv doesn't see "-" as a word separator) and Canadian and UK postal codes (mixed alphanumerics).

Cheers!

[RonMcK]

Gary,

Combining XID's and Norie's suggestions gives

[vba]For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell [/vba]
Works very nicely on my very small sample. Areas that may be problems: hyphenated names (StrConv doesn't see "-" as a word separator) and Canadian and UK postal codes (mixed alphanumerics).

Cheers!

Darn typo. That should be
StrConv(cell.Value, 3)

[GaryB]

Darn typo. That should be
StrConv(cell.Value, 3)

When I created a macro and put this code into it I get a compile error
syntax error
This is what I did for the macro.
[VBA]Sub UPPERCASE()
For Eachcell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 2) ' where 3 = vbProperCase
Next cell
End Sub[/VBA]

Thanks

Gary

[Bob Phillips]

[vba]

Sub UPPERCASE()
For Each cell In Activesheet.UsedRange
cell.Value = StrConv(cell.Value, 3) ' where 3 = vbProperCase
Next cell
End Sub
[/vba]

[GaryB]

Thanks Everyone,

The last entry by El Xid did it. Worked like a charm.

Gary

[Bob Phillips]

That wasn't by me, I just spotted the missing space.

[GaryB]

That wasn't by me, I just spotted the missing space.

Sorry,
Didn't mean to take anything away from Ron. I was refering to the fix in the code you spotted. Any Way,

Thanks again to everyone.

Gary