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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> 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
>>
>>
>>

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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > 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
> >>
> >>
> >>
>
>
>

=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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > 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

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" <ec35720@netscapeXSPAM.com> wrote in message
news:42527DE5.6F719901@netscapeXSPAM.com...
> =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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in
message
> > news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > > 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

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" <glass_patrick@hotmail.com> wrote in message
news:uTfFTHjOFHA.1932@tk2msftngp13.phx.gbl...
> 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" <ec35720@netscapeXSPAM.com> wrote in message
> news:42527DE5.6F719901@netscapeXSPAM.com...
> > =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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in
> message
> > > news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > > > 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
>
>

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" <glass_patrick@hotmail.com> wrote in message
> news:uTfFTHjOFHA.1932@tk2msftngp13.phx.gbl...
> > 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" <ec35720@netscapeXSPAM.com> wrote in message
> > news:42527DE5.6F719901@netscapeXSPAM.com...
> > > =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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in
> > message
> > > > news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > > > > 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

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" <ec35720@netscapeXSPAM.com> wrote in message
> news:42527DE5.6F719901@netscapeXSPAM.com...
> > =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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in
> message
> > > news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > > > 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

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" <ec35720@netscapeXSPAM.com> wrote in message
> news:42527DE5.6F719901@netscapeXSPAM.com...
> > =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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in
> message
> > > news:7118C344-C0EB-4606-9E83-4D9CEE4C09AD@microsoft.com...
> > > > 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