Change message to include range

[Pat]

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

 

[Bob Phillips]

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

 

[Pat]

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

 

[Norman Jones]

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?

 

[Pat]

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]

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

 

[Norman Jones]

Hi Pat,

Please replace the suggested diagnostic sub with:

Sub Info()

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


---
Regards,
Norman

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

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").


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

 

[Pat]

Norman,
I owe you an apology your first suggested solution was correct.

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

When you asked me to run code to determine if K22 was upper or lowercase I
run your code above again making sure it was in uppercase and it worked.

Cheers
Pat

Hi Pat,

Please replace the suggested diagnostic sub with:

Sub Info()

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


---
Regards,
Norman

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

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