About Us

[email protected]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
When is a cell empty and how do I empty it.	C Brandt	Excel Discussion (Misc queries)	5	August 13th 07 05:37 PM
Leaving an empty cell empty	GRL	Excel Discussion (Misc queries)	4	April 22nd 06 05:47 PM
why a reference to an empty cell is not considered empty	Nicoscot	Excel Discussion (Misc queries)	10	March 10th 06 05:36 AM
Finding next empty empty cell in a range of columns	UncleBun	Excel Programming	1	January 13th 06 11:22 PM
Empty cell and a the empty String	JE McGimpsey	Excel Programming	0	September 13th 04 04:12 PM

Menu

About Us