| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to search for a code, and have the correct information auto fi | Excel Discussion (Misc queries) | |||
| #Value! error on code that should be correct? | Excel Discussion (Misc queries) | |||
| Code for Correct location of Add-ins for all versions of Excel | Excel Programming | |||
| Help to correct code | Excel Programming | |||
| Can you help with the correct translation of this code? | Excel Programming | |||
Linear Mode
