empty cell instead of 0's

  • Thread starter Thread starter ims121uk
  • Start date Start date
I

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
 
What is the problem, MAX will get the value above 0.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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
 
The problem is not the MAX function its the macro
Could you post the macro?

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

Mike said:
Could you post the macro?

Mike F
Click to expand...
Click to expand...
 
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 said:
Could you post the macro?

Mike F
Click to expand...
Click to expand...
 
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

Mike said:
Could you post the macro?

Mike F
Click to expand...
Click to expand...
 
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 said:
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

Mike said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

Bob said:
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
Click to expand...
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
Click to expand...
of
a
colour changing method used on the total cell. Is there a way
Click to expand...
to
update
the marco to remove the 0's.

many thanks

Imran
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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.
Click to expand...
 
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 said:
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)
Click to expand...
 
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top