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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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