empty cell instead of 0's

[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

 

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

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

 

[NickHK]

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

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

 

[ims121uk]

Thanks guy for your help got the problem sorted out.

many thanks

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