Solved: Upper/Lower Case

[GaryB]

Hi,

I have an excel file that I imported from one of our databases and everything in it is capitalized. I could export it as a comma delimited file into a word processing program to make it upper and lower case type , but I was wondering if there is any way, in Excel, to do this. As usual any help is always appreciated.

Thanks,

Gary

[JimmyTheHand]

Hi Gary

Here's the code:
[vba]Sub test()
Dim c As Range
For Each c In ActiveSheet.Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell)).Cells
If Left(c.Formula, 1) <> "=" Then c.Value = LCase(c.Value)
Next c
End Sub[/vba] And here's what it does:
Takes all cells in the used range of ActiveSheet, and changes the cell values to lower case, except when there is a formula in the cell.

[GaryB]

HI Jimmy,

I see what this does and it's great but I am looking for some that will leave the first letter of each word Capatalized os end up upper and lower case type.

thanks for the help

Gary

[austenr]

if your data is in A1, place this in B1 and copy down.

[VBA]=proper(a1)[/VBA]

[JimmyTheHand]

In this case the code changes to
[vba] Dim c As Range
For Each c In ActiveSheet.Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell)).Cells
If Left(c.Formula, 1) <> "=" Then c.Value = Application.WorksheetFunction.Proper(c.Value)
Next c[/vba]
Thanks to austenr for the tip

[Bob Phillips]

I don't understand why people test cell for formula as starting with = when there is a built-in method

[vba]

If c.HasFormula Then ...
[/vba]

[JimmyTheHand]

I don't understand why people test cell for formula as starting with = when there is a built-in method

[vba]

If c.HasFormula Then ...
[/vba]

That's probably because people don't know about the built-in method...
I was ignorant, too, but not anymore I mean, not that ignorant.
Thanks for the tip

[Bob Phillips]

That's probably because people don't know about the built-in method...
I was ignorant, too, but not anymore

Spreading the word ...

[GaryB]

Thanks to everyone for the help. The code did what I needed it to do.

Thanks again,
Gary

[Zack Barresse]

Also, the special cells last cell can be thrown off and is not really reliable. Either use the Cells.Find("*") or the Rows.Count method. Or another way to not loop through all cells is to use the special cells formulas. However you would need to error trap it as if there were no formulas it would error out that line of code. Should prove faster.

HTH