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
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" <glass_patrick@hotmail.com> wrote in message
news:u%23NF%23OUEFHA.2508@TK2MSFTNGP09.phx.gbl...
> 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
>
>
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" <twogilvy@msn.com> wrote in message
news:Ohx1UWUEFHA.1188@tk2msftngp13.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:u%23NF%23OUEFHA.2508@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
>
>
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" <glass_patrick@hotmail.com> wrote in message
news:%23nEEq3UEFHA.2180@TK2MSFTNGP10.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:Ohx1UWUEFHA.1188@tk2msftngp13.phx.gbl...
> > 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" <glass_patrick@hotmail.com> wrote in message
> > news:u%23NF%23OUEFHA.2508@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>
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" <twogilvy@msn.com> wrote in message
news:%2315jaIVEFHA.624@TK2MSFTNGP09.phx.gbl...
> 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" <glass_patrick@hotmail.com> wrote in message
> news:%23nEEq3UEFHA.2180@TK2MSFTNGP10.phx.gbl...
> > 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" <twogilvy@msn.com> wrote in message
> > news:Ohx1UWUEFHA.1188@tk2msftngp13.phx.gbl...
> > > 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" <glass_patrick@hotmail.com> wrote in message
> > > news:u%23NF%23OUEFHA.2508@TK2MSFTNGP09.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
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" <glass_patrick@hotmail.com> wrote in message
news:eXt%230hVEFHA.3492@TK2MSFTNGP12.phx.gbl...
> 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" <twogilvy@msn.com> wrote in message
> news:%2315jaIVEFHA.624@TK2MSFTNGP09.phx.gbl...
> > 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" <glass_patrick@hotmail.com> wrote in message
> > news:%23nEEq3UEFHA.2180@TK2MSFTNGP10.phx.gbl...
> > > 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" <twogilvy@msn.com> wrote in message
> > > news:Ohx1UWUEFHA.1188@tk2msftngp13.phx.gbl...
> > > > 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" <glass_patrick@hotmail.com> wrote in message
> > > > news:u%23NF%23OUEFHA.2508@TK2MSFTNGP09.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
You can use this macro, just select desired cells and run macro
Sub malaVelika()
Dim pom
For Each celija In Selection.Cells
If IsNull(celija.Value) Then
GoTo prazan
ElseIf IsNumeric(celija.Value) Then GoTo prazan
Else
pom = UCase(celija.Value)
celija.Value = pom
End If
prazan:
Next
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks