empty cell instead of 0's

[ims121uk]

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

 

[Bob Phillips]

What is the problem, MAX will get the value above 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Try this instead of a single MAX(...)

=IF(MAX(...)=0,"",MAX(...))

Regards,
Stefi


„[email protected]†ezt írta:

 

[ims121uk]

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

 

[Mike Fogleman]

The problem is not the MAX function its the macro
Could you post the macro?

Mike F

 

[ims121uk]

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

 

[Dave Peterson]

Check for numbers first:

=if(Count(a1:a4)=0,"",max(a1:a4))

 

[Bob Phillips]

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)

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

Could you post the macro?

Mike F

 

[Bob Phillips]

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)

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

Could you post the macro?

Mike F

 

[Mike Fogleman]

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

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

Could you post the macro?

Mike F

 

[Mike Fogleman]

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),"""")"

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

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

The problem is not the MAX function its the macro
Could you post the macro?

Mike F
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)

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

 

[ims121uk]

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.

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)

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

The problem is not the MAX function its the macro
Could you post the macro?

Mike F
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)

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

 

[Bob Phillips]

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)

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.

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)

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

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

 

[ims121uk]

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.

 

[Bob Phillips]

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)

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.

 

[ims121uk]

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

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)

 

[Dave Peterson]

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?

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

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)

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





(e-mail address removed) 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

 

[Bob Phillips]

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)

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?

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

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)

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





(e-mail address removed) 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]

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.

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)

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?

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)

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





(e-mail address removed) 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

 

[ims121uk]

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?

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.

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)

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?

(e-mail address removed) 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)

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





(e-mail address removed) 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