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

U¿ytkownik "Pat" <glass_patrick@hotmail.com> napisa³ w wiadomo¶ci
news:eRJDc1VOFHA.3988@tk2msftngp13.phx.gbl...
> 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

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
>
>
>

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
>
>

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