|
empty cell instead of 0's
I am using a MAX function that gathered results from four cells. But
when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
What is the problem, MAX will get the value above 0.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Try this instead of a single MAX(...)
=IF(MAX(...)=0,"",MAX(...)) Regards, Stefi ezt *rta: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
The problem is not the MAX function its the macro where it updates the
cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
The problem is not the MAX function its the macro
Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Here is the macro code: -
The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Sub RefreshISSUES1()
Dim sFormula As String sFormula = "=IF(MAX(R[1]C:R[4]C)=0,"""",MAX(R[1]C:R[4]C))" Range("D38").FormulaR1C1 = sFormula Range("E38").FormulaR1C1 = sFormula Range("F38").FormulaR1C1 = sFormula Range("G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Sorry, we can do better than that
Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(MAX(R[1]C:R[4]C)=0,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Only put a number greater than 0:
ActiveCell.FormulaR1C1 = "=IF(MAX(R[1]C:R[4]C)0,MAX(R[1]C:R[4]C),"")" Mike F wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
Bob's formula was right, I left out one set of quotes:
ActiveCell.FormulaR1C1 = "=IF(MAX(R[1]C:R[4]C)0,MAX(R[1]C:R[4]C),"""")" "Mike Fogleman" wrote in message m... Only put a number greater than 0: ActiveCell.FormulaR1C1 = "=IF(MAX(R[1]C:R[4]C)0,MAX(R[1]C:R[4]C),"")" Mike F wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
thanks for you help, but i have still got a problem because 0 should be
display if it selected from the list 0 to 5. If the cells are blanks total cell should be blank. Bob Phillips wrote: Sorry, we can do better than that Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(MAX(R[1]C:R[4]C)=0,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
I think I (we?) are missing something here.
What we gave was a formula that will print the MAX value if there is one, if it is 0 it prints blank. What does ... because 0 should be display if it selected from the list 0 to 5 ... mean? And what total cell are you referring to? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... thanks for you help, but i have still got a problem because 0 should be display if it selected from the list 0 to 5. If the cells are blanks total cell should be blank. Bob Phillips wrote: Sorry, we can do better than that Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(MAX(R[1]C:R[4]C)=0,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Here is the macro code: - The macro is in place for updating colour change Sub RefreshISSUES1() ' ' Range("D38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("E38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("F38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("G38").Select ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[4]C)" Range("A38:C38").Select End Sub Mike Fogleman wrote: The problem is not the MAX function its the macro Could you post the macro? Mike F wrote in message oups.com... The problem is not the MAX function its the macro where it updates the cell total and a 0 would appear. Is there a way around this to appear blank when cells are blank instead of 0? regards Imran Bob Phillips wrote: What is the problem, MAX will get the value above 0. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran |
empty cell instead of 0's
I mean for example if the four cell equal blank the total cell should
be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson |
empty cell instead of 0's
Aah!
Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson |
empty cell instead of 0's
thanks Bob for this code, a errors stills pop up and doesn't work.
Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson |
empty cell instead of 0's
I think you should restate your requirements.
If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
which conflicts with the previous, clear (to me then at least) statement of
.... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
If I were the original poster, I think I would try to add the formulas manually
(using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
That is clear!
if the four cell equal blank the total cell should be blank and when its 0 it should be 0. How is this acheived? Dave Peterson wrote: If I were the original poster, I think I would try to add the formulas manually (using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
That is clear!
if the four cell equal blank the total cell should be blank and when its 0 it should be 0. How is this acheived? Dave Peterson wrote: If I were the original poster, I think I would try to add the formulas manually (using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
It's not clear to me what you want.
But you can check the number of cells that have numbers in them with =count(a1:a4) Like: =if(Count(a1:a4)=0,"",max(a1:a4)) or =if(Count(a1:a4)<4,"",max(a1:a4)) wrote: That is clear! if the four cell equal blank the total cell should be blank and when its 0 it should be 0. How is this acheived? Dave Peterson wrote: If I were the original poster, I think I would try to add the formulas manually (using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
That's what Bob posted earlier.
Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub NickHK egroups.com... That is clear! if the four cell equal blank the total cell should be blank and when its 0 it should be 0. How is this acheived? Dave Peterson wrote: If I were the original poster, I think I would try to add the formulas manually (using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson |
empty cell instead of 0's
Thanks guy for your help got the problem sorted out.
many thanks wrote: That is clear! if the four cell equal blank the total cell should be blank and when its 0 it should be 0. How is this acheived? Dave Peterson wrote: If I were the original poster, I think I would try to add the formulas manually (using countblank or count or whatever). Then when I got that figured out the way I want, I'd try to put it into code. Maybe working in the worksheet would make the requirements clearer....or maybe not. Bob Phillips wrote: which conflicts with the previous, clear (to me then at least) statement of ... if the four cell equal blank the total cell should be blank and when its 0 it should be 0 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave Peterson" wrote in message ... I think you should restate your requirements. If any one to four cell equals blank, then the total should be blank goes against the next statement: when any 0 from one to four cells, then the total should be 0. If you have this in A1:A4: 0 (blank) 0 (blank) You have some blanks and some 0's. What should happen? Should it be 0 or should it be blank? wrote: thanks Bob for this code, a errors stills pop up and doesn't work. Sorry for being a pain the real formulas should be if any one to four cell equal blank the total cell should be blank and when any 0 from one to four cells the total cell should be 0 because of a colour change formatting allowed 0 to change colour to red. Cheers Bob Phillips wrote: Aah! Sub RefreshISSUES1() Dim sFormula As String sFormula = "=IF(COUNTBLANK(R[1]C:R[4]C)=4,"""",MAX(R[1]C:R[4]C))" Range("D38:G38").FormulaR1C1 = sFormula Range("A38:C38").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I mean for example if the four cell equal blank the total cell should be blank and when its 0 it should be 0 because of a colour change formatting allowed 0 to change colour to red. Dave Peterson wrote: Check for numbers first: =if(Count(a1:a4)=0,"",max(a1:a4)) wrote: I am using a MAX function that gathered results from four cells. But when all cells are blank the total cell is 0. I have already tried unchecking zeros values from tools options, which works but i need to use zero in other cells. I used a marco to update cells because of a colour changing method used on the total cell. Is there a way to update the marco to remove the 0's. many thanks Imran -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 06:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com