Change to UPERCASE
 

Hello,

I have to upload an Excel file everyday to a database. Is there any way I
can change the text in one column on the Excel file to UPPERCASE before I
upload the file to the DB?

Thanks a lot
--
Bob

Change to UPERCASE
 

Hi Bob

This macro will change all text cells in the selection to uppercase
you can change it to a column if you want (you don't have to select the range then)

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob" wrote in message ...
Hello,

I have to upload an Excel file everyday to a database. Is there any way I
can change the text in one column on the Excel file to UPPERCASE before I
upload the file to the DB?

Thanks a lot
--
Bob

Change to UPERCASE
 

Sub MakeUpperCase()
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,"C"),.Cells(rows.count,"C").End(xl up))
End With
for each cell in rng
cell.Value = Ucase(Cell.Value)
Next
End Sub

--
Regards,
Tom Ogilvy


"Bob" wrote in message
...
Hello,

I have to upload an Excel file everyday to a database. Is there any way I
can change the text in one column on the Excel file to UPPERCASE before I
upload the file to the DB?

Thanks a lot
--
Bob

Change to UPERCASE
 

For Each c In Selection.Cells
c.Value = UCase$(c.Value)
Next c

Change to UPERCASE
 

Hi Paul,
There are a couple of problems with your solution that you
may notice would not be a problem in the other solutions.
1) You will wipe out formulas if any are present
2) The use of Selection with no other limiting criteria does not
make for an intuitive solution. The user must not select an
entire column but only the cells to be changed; otherwise, the
macro may take a long time to complete.

There are some other things that help with speed when a complete
solution is provided. The use of Special Cells automatically limits
a range to within the used range.

You can take a look at the comments that go along with
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Paul D. Simon" wrote in message ups.com...

For Each c In Selection.Cells
c.Value = UCase$(c.Value)
Next c

Change to UPERCASE
 

Thanks very much David. You bring to light excellent and important
points that I had not thought of in my attempt to make the code as
small as possible (especially the extremely dangerous/disastrous result
of unknowingly changing formulas to values). Hopefully, others who saw
my solution will take note of your response. (I'll be altering my code
accordingly.)

Many thanks,
Paul

Change to UPERCASE
 

Hi Paul,
You're welcome. Big advantage of newsgroups is to have a
selection and choose the best for your use, and sometimes
to learn if you really have a good solution.

The page of mine I referred you to (proper.htm) was an attempt at
writing the best solutions that I could, and to help you identify when
you have several solutions what to look for to help you
identify what makes an efficient solution, a generic solution,
how complete a solution is, and hopefully be understandable.

It also helps to have a slow machine that identifies bottlenecks,
some things improve very dramatically with more RAM and faster
CPU. For instance your solution applied to one column I know
would have taken 3 minutes to do one column with 128MB RAM,
on a 600mHz machine running Windows 2000, and with the same
setup almost instantly once restricted to a more reasonable actually
used range.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Paul D. Simon" wrote in message oups.com...
Thanks very much David. You bring to light excellent and important
points that I had not thought of in my attempt to make the code as
small as possible (especially the extremely dangerous/disastrous result
of unknowingly changing formulas to values). Hopefully, others who saw
my solution will take note of your response. (I'll be altering my code
accordingly.)

Many thanks,
Paul