| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| custom format - month as uppercase | Excel Discussion (Misc queries) | |||
| Need an UPPERCASE format ability in Excel just like Word | Excel Discussion (Misc queries) | |||
| How to format cells for uppercase entry | New Users to Excel | |||
| Format Excel column as Upper Case without use of UPPERCASE formul | Excel Discussion (Misc queries) | |||
| How to start every word in a cell uppercase? | Excel Discussion (Misc queries) | |||
Hybrid Mode
