Format cell to uppercase using code
 

When K11 contains the value "QS" and it is entered in lowercase I want to
use code to convert the characters to uppercase. The cell is formatted to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to complete it.

If Not IsEmpty(Cells(21, 11)) Then
..Value = "qs"

Thanks if you can help.
Pat

Format cell to uppercase using code
 

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = "QS"

or

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = Ucase("qs")

if it could have a string like qs123
or ABqsCA

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

--
Regards,
Tom Ogilvy




"Pat" wrote in message
...
When K11 contains the value "QS" and it is entered in lowercase I want to
use code to convert the characters to uppercase. The cell is formatted to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to complete it.

If Not IsEmpty(Cells(21, 11)) Then
.Value = "qs"

Thanks if you can help.
Pat

Format cell to uppercase using code
 

Thank you for helping out. The solution provided does not allow other data
to be entered into the cell. The code should only used to convert "qs" to
uppercase if "qs" is found to be in lowercase. If other data is found in the
target cell this data should take precedence over "QS" In other words allow
any data to be entered into the cell but should "QS" happen to be entered
into the cell and it has accidentally been entered in lowercase convert it
to uppercase.


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = "QS"

or

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = Ucase("qs")

if it could have a string like qs123
or ABqsCA

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

--
Regards,
Tom Ogilvy




"Pat" wrote in message
...
When K11 contains the value "QS" and it is entered in lowercase I want
to
use code to convert the characters to uppercase. The cell is formatted
to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to complete
it.

If Not IsEmpty(Cells(21, 11)) Then
.Value = "qs"

Thanks if you can help.
Pat

Format cell to uppercase using code
 

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

Does exactly what you ask.

Just to demonstate from the immediate window:

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = "qs " & ActiveCell.Value & "s q qs"

? activeCell.Value
qs Patrick doesn't test code before postings q qs

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
QS Patrick doesn't test code before postings q QS


--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Thank you for helping out. The solution provided does not allow other
data
to be entered into the cell. The code should only used to convert "qs" to
uppercase if "qs" is found to be in lowercase. If other data is found in
the
target cell this data should take precedence over "QS" In other words
allow
any data to be entered into the cell but should "QS" happen to be entered
into the cell and it has accidentally been entered in lowercase convert it
to uppercase.


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = "QS"

or

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = Ucase("qs")

if it could have a string like qs123
or ABqsCA

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

--
Regards,
Tom Ogilvy




"Pat" wrote in message
...
When K11 contains the value "QS" and it is entered in lowercase I want
to
use code to convert the characters to uppercase. The cell is
formatted
to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to complete
it.

If Not IsEmpty(Cells(21, 11)) Then
.Value = "qs"

Thanks if you can help.
Pat

Format cell to uppercase using code
 

Thank you Tom that solved it. You mention to test the code in the immediate
window, I have no experience of testing code in this way. I have tried on
this occasion and was unsuccessful in figuring out how to work the immediate
window. Help files were not clear. Anyway the problem is fixed and many
thanks for that.
Cheers
Pat


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

Does exactly what you ask.

Just to demonstate from the immediate window:

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = "qs " & ActiveCell.Value & "s q qs"

? activeCell.Value
qs Patrick doesn't test code before postings q qs

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
QS Patrick doesn't test code before postings q QS


--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Thank you for helping out. The solution provided does not allow other
data
to be entered into the cell. The code should only used to convert "qs"
to
uppercase if "qs" is found to be in lowercase. If other data is found in
the
target cell this data should take precedence over "QS" In other words
allow
any data to be entered into the cell but should "QS" happen to be
entered
into the cell and it has accidentally been entered in lowercase convert
it
to uppercase.


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = "QS"

or

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = Ucase("qs")

if it could have a string like qs123
or ABqsCA

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

--
Regards,
Tom Ogilvy




"Pat" wrote in message
...
When K11 contains the value "QS" and it is entered in lowercase I
want
to
use code to convert the characters to uppercase. The cell is
formatted
to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to
complete
it.

If Not IsEmpty(Cells(21, 11)) Then
.Value = "qs"

Thanks if you can help.
Pat

Format cell to uppercase using code
 

If you enter an executable command in the immediate window and hit enter at
the end, it executes.

If you want to query something for its value, you would do

? activeCell.value

for example.

Its particularly good for testing out concatenation of string

sStr1 = "---"
sStr2 = "<---"
? sStr1 & " abcd " & sStr2
--- abcd <---

for example. enter is hit at the end of each line except the last which is
the result of the ? sStr . . .

--
Regards,
Tom Ogilvy



"Pat" wrote in message
...
Thank you Tom that solved it. You mention to test the code in the
immediate
window, I have no experience of testing code in this way. I have tried on
this occasion and was unsuccessful in figuring out how to work the
immediate
window. Help files were not clear. Anyway the problem is fixed and many
thanks for that.
Cheers
Pat


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

Does exactly what you ask.

Just to demonstate from the immediate window:

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
Patrick doesn't test code before posting

ActiveCell.Value = "qs " & ActiveCell.Value & "s q qs"

? activeCell.Value
qs Patrick doesn't test code before postings q qs

ActiveCell.Value = _
Application.Substitute(ActiveCell.Value,"qs","QS")

? activeCell.Value
QS Patrick doesn't test code before postings q QS


--
Regards,
Tom Ogilvy

"Pat" wrote in message
...
Thank you for helping out. The solution provided does not allow other
data
to be entered into the cell. The code should only used to convert "qs"
to
uppercase if "qs" is found to be in lowercase. If other data is found
in
the
target cell this data should take precedence over "QS" In other words
allow
any data to be entered into the cell but should "QS" happen to be
entered
into the cell and it has accidentally been entered in lowercase
convert
it
to uppercase.


"Tom Ogilvy" wrote in message
...
If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = "QS"

or

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = Ucase("qs")

if it could have a string like qs123
or ABqsCA

If Not IsEmpty(Cells(21, 11)) Then _
Cells(21, 11).Value = _
Application.Substitute(cells(21,11),"qs","QS")

--
Regards,
Tom Ogilvy




"Pat" wrote in message
...
When K11 contains the value "QS" and it is entered in lowercase I
want
to
use code to convert the characters to uppercase. The cell is
formatted
to
accept text and can contain other values.

Here is my attempt at writing part of the code. Need help to
complete
it.

If Not IsEmpty(Cells(21, 11)) Then
.Value = "qs"

Thanks if you can help.
Pat