Code is not correct
 

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete this
data. You cannot have any data in the Quantity Ordered column when you are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat

Code is not correct
 

U¿ytkownik "Pat" napisa³ w wiadomo¶ci
...
The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete
this
data. You cannot have any data in the Quantity Ordered column when you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat


try to
MsgBox "There is data in column I28:I950, find and delete this data." & _
vbNewLine & "You cannot have any data in the Quantity Ordered
column when you are" & _
vbNewLine & "creating a Quick Sale invoice."

mcg

Code is not correct
 

What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete this
data. You cannot have any data in the Quantity Ordered column when you are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat

Code is not correct
 

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula as
mentioned will be in the range.


"Tushar Mehta" wrote in message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete
this
data. You cannot have any data in the Quantity Ordered column when you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat

Code is not correct
 

As the help for COUNTA indicates, it will count any *non empty* cell. XL
defines a non-empty cell as one that contains anything in it including a
formula that makes it *appear* that the cell is empty (as in your case).

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

Just for the record COUNTIF(E1:F10,"=") will count cells that are truly
empty. Don't ask me why.

"Pat" wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula as
mentioned will be in the range.


"Tushar Mehta" wrote in message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete
this
data. You cannot have any data in the Quantity Ordered column when you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat

Code is not correct
 

=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula as
mentioned will be in the range.

"Tushar Mehta" wrote in message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and delete
this
data. You cannot have any data in the Quantity Ordered column when you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson

Code is not correct
 

Hi folks,

Thank you for helping me out. I have been trying out our suggestions and I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale in
any of the cells in the range I28:I950 the MsgBox should warn the user.


-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat




"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson

Code is not correct
 

The only other option open to me if I cannot get the correct code is to
incorporate code that would delete whatever is in the range I28:I950 if
"QS" is in cell:
If Cells(21, 11).Value = "QS" Then

Any ideas how this could be achieved?

Pat

"Pat" wrote in message
...
Hi folks,

Thank you for helping me out. I have been trying out our suggestions and
I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale
in
any of the cells in the range I28:I950 the MsgBox should warn the user.


-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat




"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson

Code is not correct
 

How about this one that uses =countblank() to look for cells that look empty:

Dim myRng As Range
Set myRng = Range("I28:I950")

If Application.CountBlank(myRng) = myRng.Cells.Count Then
MsgBox "they all LOOK empty"
Else
MsgBox "something besides """" in those cells"
End If




Pat wrote:

Hi folks,

Thank you for helping me out. I have been trying out our suggestions and I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale in
any of the cells in the range I28:I950 the MsgBox should warn the user.

-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat

"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson

--

Dave Peterson

Code is not correct
 

if cells(21,11).value = "QS" then
range("i28:i950").clearcontents
end if



Pat wrote:

The only other option open to me if I cannot get the correct code is to
incorporate code that would delete whatever is in the range I28:I950 if
"QS" is in cell:
If Cells(21, 11).Value = "QS" Then

Any ideas how this could be achieved?

Pat

"Pat" wrote in message
...
Hi folks,

Thank you for helping me out. I have been trying out our suggestions and
I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale
in
any of the cells in the range I28:I950 the MsgBox should warn the user.


-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat




"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson



--

Dave Peterson

Code is not correct
 

ps. You're right about that formula. I only checked with text--not numbers.

Pat wrote:

Hi folks,

Thank you for helping me out. I have been trying out our suggestions and I
have been not getting the result I am looking for.

If Application.CountIf(Range("I28:I950"), """") 0 Then

The above did not return the message from the MsgBox as I have tried out
having a value greater than 0 in the range I28:I950. If there is a vale in
any of the cells in the range I28:I950 the MsgBox should warn the user.

-----------------------------

If all the cells in I28:I950 contain the formula you mention, use
COUNTIF(E1:F10,"") This will count cells that are either truly empty or
appear empty because of a formula yields "".

I am not sure where E1:F10 comes from but I have modified the formula in
I28:I950 to look like:

=COUNTIF(J76,"=")

instead of:

=IF(J76="","",J76)

By using what I think is your suggestion this will yield 1 if J76 is empty
and empty if J76 contains a value.

Unfortunately this is not what is needed.

Regards
Pat

"Dave Peterson" wrote in message
...
=counta() includes all formulas--including those that evaluate to "".

Maybe you could use a different formula:

If Application.CountIf(Range("I28:I950"), """") 0 Then

(I like application.countif, but you could still use
worksheetfunction.countif.)

Pat wrote:

The messagebox appears even though there is no data in I28:I950
For some reason it thinks there is data in the range, only the formula
as
mentioned will be in the range.

"Tushar Mehta" wrote in
message
...
What do you want it to do?

"Pat" wrote:

The following code is not correct.


If WorksheetFunction.CountA(Range("I28:I950")) 0 Then
If Cells(21, 11).Value = "QS" Then
MsgBox "There is data in column I28:I950, find and
delete
this
data. You cannot have any data in the Quantity Ordered column when
you
are
creating a Quick Sale invoice. "
Exit Sub
End If
End If


There is a formula in cell in the range I28:I950
For eg. =IF(J76="","",J76)


I will appreciate any and all help.

Thanks
Pat




--

Dave Peterson

--

Dave Peterson