Change message to include range

The want to change the following code:

If Cells(22, 11).Value = "QS" Then
MsgBox "Previous code"
Exit Sub
End If

To this:

If IsNumeric("I24:I1000").Value
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column I24:I1000, find and delete this
data"
Exit Sub
End If

The change to the code is to inform the user that there is data in the
range I24:I1000

IF Worksheetfunction.COUNTA(Range("I24:I1000")) = _
Worksheetfunction.COUNT(Range("I24:I1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column I24:I1000, find and delete this
data"
Exit Sub
End If
End If

--
HTH

Bob Phillips

"Pat" <glass_patrick@hotmail.com> wrote in message
news:e1B1xJA$EHA.3840@tk2msftngp13.phx.gbl...
> The want to change the following code:
>
> If Cells(22, 11).Value = "QS" Then
> MsgBox "Previous code"
> Exit Sub
> End If
>
> To this:
>
> If IsNumeric("I24:I1000").Value
> If Cells(22, 11).Value = "QS" Then
> MsgBox "There is data in column I24:I1000, find and delete this
> data"
> Exit Sub
> End If
>
> The change to the code is to inform the user that there is data in the
> range I24:I1000
>
>

I am afraid nothing happened when the code was run. There was data in cells
I98, 103, 107, 115 & 123 which the code should have detected. Here is the
code in its entirety:

Private Sub CommandButton1_Click()
If WorksheetFunction.CountA(Range("I28:I1000")) = _
WorksheetFunction.Count(Range("I28:I1000")) Then
If Cells(22, 11).Value = "QS" Then
MsgBox "There is data in column I28:I1000, find and delete this
data "
Exit Sub
End If
End If
End Sub

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:ezO$zaA$EHA.1400@TK2MSFTNGP11.phx.gbl...
>
> IF Worksheetfunction.COUNTA(Range("I24:I1000")) = _
> Worksheetfunction.COUNT(Range("I24:I1000")) Then
> If Cells(22, 11).Value = "QS" Then
> MsgBox "There is data in column I24:I1000, find and delete
this
> data"
> Exit Sub
> End If
> End If
>
> --
> HTH
>
> Bob Phillips
>
> "Pat" <glass_patrick@hotmail.com> wrote in message
> news:e1B1xJA$EHA.3840@tk2msftngp13.phx.gbl...
> > The want to change the following code:
> >
> > If Cells(22, 11).Value = "QS" Then
> > MsgBox "Previous code"
> > Exit Sub
> > End If
> >
> > To this:
> >
> > If IsNumeric("I24:I1000").Value
> > If Cells(22, 11).Value = "QS" Then
> > MsgBox "There is data in column I24:I1000, find and delete this
> > data"
> > Exit Sub
> > End If
> >
> > The change to the code is to inform the user that there is data in the
> > range I24:I1000
> >
> >
>
>

Hi Pat,

PMFBI but I suspect that Bob is enjoying a well earned break or (more
likely) working on further improving his web site).


Re: Range("I28:I1000")

It is not clear (to me) from you preceding post whether you want to invoke
the MsgBox if:

(1) all populated cells in the range are numeric;

(2) if any of the range cells are numeric, or

(3) if any of the cells have any kind of data.

If the first condition pertains (which is, I think, Bob's interpretation)
try changing:

> If WorksheetFunction.CountA(Range("I28:I1000")) = _
> WorksheetFunction.Count(Range("I28:I1000")) Then

to

If WorksheetFunction.Count(Range("I28:I1000")) = _
WorksheetFunction.CountA(Range("I28:I1000")) Then

(i.e. simply reverse CountA and Count).
----------------------------------------------------------

If (2) should apply, then try changing:

> If WorksheetFunction.CountA(Range("I28:I1000")) = _
> WorksheetFunction.Count(Range("I28:I1000")) Then

to:

If WorksheetFunction.Count(Range("I28:I1000")) >0 Then
-------------------------------------------------------

Finally, if (3) pertains, try changing :

> If WorksheetFunction.CountA(Range("I28:I1000")) = _
> WorksheetFunction.Count(Range("I28:I1000")) Then

to

If WorksheetFunction.CountA(Range("I28:I1000")) >0 Then

If none of these interpretations is correct, perhaps you could provide
additional explanation.

Incidentally, do you have a reason for asking thre user to find and delete
data in the specified range rather than adapting your macro to do this?

---
Regards,
Norman



"Pat" <glass_patrick@hotmail.com> wrote in message
news:OW0LxSB$EHA.3416@TK2MSFTNGP09.phx.gbl...
>I am afraid nothing happened when the code was run. There was data in
>cells
> I98, 103, 107, 115 & 123 which the code should have detected. Here is the
> code in its entirety:
>
> Private Sub CommandButton1_Click()
> If WorksheetFunction.CountA(Range("I28:I1000")) = _
> WorksheetFunction.Count(Range("I28:I1000")) Then
> If Cells(22, 11).Value = "QS" Then
> MsgBox "There is data in column I28:I1000, find and delete
> this
> data "
> Exit Sub
> End If
> End If
> End Sub
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:ezO$zaA$EHA.1400@TK2MSFTNGP11.phx.gbl...
>>
>> IF Worksheetfunction.COUNTA(Range("I24:I1000")) = _
>> Worksheetfunction.COUNT(Range("I24:I1000")) Then
>> If Cells(22, 11).Value = "QS" Then
>> MsgBox "There is data in column I24:I1000, find and delete
> this
>> data"
>> Exit Sub
>> End If
>> End If
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "Pat" <glass_patrick@hotmail.com> wrote in message
>> news:e1B1xJA$EHA.3840@tk2msftngp13.phx.gbl...
>> > The want to change the following code:
>> >
>> > If Cells(22, 11).Value = "QS" Then
>> > MsgBox "Previous code"
>> > Exit Sub
>> > End If
>> >
>> > To this:
>> >
>> > If IsNumeric("I24:I1000").Value
>> > If Cells(22, 11).Value = "QS" Then
>> > MsgBox "There is data in column I24:I1000, find and delete this
>> > data"
>> > Exit Sub
>> > End If
>> >
>> > The change to the code is to inform the user that there is data in the
>> > range I24:I1000
>> >
>> >
>>
>>
>
>

Hello Norman,

I am afraid none of the options you kindly submitted worked. The data
contained in Range("I28:I1000")
is the result of a formula. Why I want to delete the result of any of the
formulas in the range is because it must conform to the value in K22 which
is "QS" Any other values in K22 will not result in deleting and information
in Range("I28:I1000").

> Incidentally, do you have a reason for asking the user to find and delete
> data in the specified range rather than adapting your macro to do this?

By all means adapting the macro to delete data would be beneficial to the
user. Would a simple

Range("I28:I1000").Select
Selection.ClearContents

be what you are thinking or have you a better way?

regards
Pat

"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:uZmlQZC$EHA.3336@TK2MSFTNGP11.phx.gbl...
> Hi Pat,
>
> PMFBI but I suspect that Bob is enjoying a well earned break or (more
> likely) working on further improving his web site).
>
>
> Re: Range("I28:I1000")
>
> It is not clear (to me) from you preceding post whether you want to invoke
> the MsgBox if:
>
> (1) all populated cells in the range are numeric;
>
> (2) if any of the range cells are numeric, or
>
> (3) if any of the cells have any kind of data.
>
> If the first condition pertains (which is, I think, Bob's interpretation)
> try changing:
>
> > If WorksheetFunction.CountA(Range("I28:I1000")) = _
> > WorksheetFunction.Count(Range("I28:I1000")) Then
>
> to
>
> If WorksheetFunction.Count(Range("I28:I1000")) = _
> WorksheetFunction.CountA(Range("I28:I1000")) Then
>
> (i.e. simply reverse CountA and Count).
> ----------------------------------------------------------
>
> If (2) should apply, then try changing:
>
> > If WorksheetFunction.CountA(Range("I28:I1000")) = _
> > WorksheetFunction.Count(Range("I28:I1000")) Then
>
> to:
>
> If WorksheetFunction.Count(Range("I28:I1000")) >0 Then
> -------------------------------------------------------
>
> Finally, if (3) pertains, try changing :
>
> > If WorksheetFunction.CountA(Range("I28:I1000")) = _
> > WorksheetFunction.Count(Range("I28:I1000")) Then
>
> to
>
> If WorksheetFunction.CountA(Range("I28:I1000")) >0 Then
>
> If none of these interpretations is correct, perhaps you could provide
> additional explanation.
>
> Incidentally, do you have a reason for asking thre user to find and delete
> data in the specified range rather than adapting your macro to do this?
>
> ---
> Regards,
> Norman
>
>
>
> "Pat" <glass_patrick@hotmail.com> wrote in message
> news:OW0LxSB$EHA.3416@TK2MSFTNGP09.phx.gbl...
> >I am afraid nothing happened when the code was run. There was data in
> >cells
> > I98, 103, 107, 115 & 123 which the code should have detected. Here is
the
> > code in its entirety:
> >
> > Private Sub CommandButton1_Click()
> > If WorksheetFunction.CountA(Range("I28:I1000")) = _
> > WorksheetFunction.Count(Range("I28:I1000")) Then
> > If Cells(22, 11).Value = "QS" Then
> > MsgBox "There is data in column I28:I1000, find and delete
> > this
> > data "
> > Exit Sub
> > End If
> > End If
> > End Sub
> >
> > "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> > news:ezO$zaA$EHA.1400@TK2MSFTNGP11.phx.gbl...
> >>
> >> IF Worksheetfunction.COUNTA(Range("I24:I1000")) = _
> >> Worksheetfunction.COUNT(Range("I24:I1000")) Then
> >> If Cells(22, 11).Value = "QS" Then
> >> MsgBox "There is data in column I24:I1000, find and delete
> > this
> >> data"
> >> Exit Sub
> >> End If
> >> End If
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "Pat" <glass_patrick@hotmail.com> wrote in message
> >> news:e1B1xJA$EHA.3840@tk2msftngp13.phx.gbl...
> >> > The want to change the following code:
> >> >
> >> > If Cells(22, 11).Value = "QS" Then
> >> > MsgBox "Previous code"
> >> > Exit Sub
> >> > End If
> >> >
> >> > To this:
> >> >
> >> > If IsNumeric("I24:I1000").Value
> >> > If Cells(22, 11).Value = "QS" Then
> >> > MsgBox "There is data in column I24:I1000, find and delete
this
> >> > data"
> >> > Exit Sub
> >> > End If
> >> >
> >> > The change to the code is to inform the user that there is data in
the
> >> > range I24:I1000
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Hi Pat,

> I am afraid none of the options you kindly submitted worked. The data
> contained in Range("I28:I1000")
> is the result of a formula.

The Count and CountA functions, used in all responses to you , respond to
formula-returned values in the same way as they would to directly entered
data. So clearly there is something else going on here!

When you say:

> I am afraid none of the options you kindly submitted worked

I am not sure that I understand. Are you saying that the formulas in
Range("I28:I1000") return values but the supplied code does not cause the
msgbox to report this fact? I believe that if this is your experience, then
the problem probaly relates to Cell k22,

Please do two things:

(1) Please respond to the question implicit in my previous post, nanely: do
you want to delete data if the range is (a)all numeric, (b) part numeric or
(c) alpha and/or numeric. (d) some other condition

(2) With the relevant sheet active, run this simple sub and report the
response:

Sub Info()

If [k22].Value = "QS" Then
MsgBox "Uppercase!"
ElseIf UCase([k22]) = "QS" Then
MsgBox = "Other case"
ElseIf InStr([k22], "QS", vbTextCompare) > 0 Then
MsgBox "SUBSTRING!"
Else
MsgBox "Not Found!"
End If
End Sub

---
Regards,
Norman



"Pat" <glass_patrick@hotmail.com> wrote in message
news:%23l%2398wC$EHA.2584@TK2MSFTNGP09.phx.gbl...
> Hello Norman,
>
> I am afraid none of the options you kindly submitted worked. The data
> contained in Range("I28:I1000")
> is the result of a formula. Why I want to delete the result of any of the
> formulas in the range is because it must conform to the value in K22 which
> is "QS" Any other values in K22 will not result in deleting and
> information
> in Range("I28:I1000").
>
>> Incidentally, do you have a reason for asking the user to find and delete
>> data in the specified range rather than adapting your macro to do this?
>
> By all means adapting the macro to delete data would be beneficial to the
> user. Would a simple
>
> Range("I28:I1000").Select
> Selection.ClearContents
>
> be what you are thinking or have you a better way?
>
> regards
> Pat
>