If you really want a macro, how about:

copy the entire range
paste as values
select that pasted range
edit|replace
what: 0 (match entire cell contents!)
with: (leave blank)
replace all

linda wrote:

i've done that already but the chart still include the data which the formula
returns zero.what i'm doing right now is copy the value of cell via macro to
another area for me to create the chart but its running too slow.sometimes,it
takes hours.
this is my macro:
Do While Cells(a, b).Value < ""
x = a
Do While Cells(x, b).Value < ""
Cells(x, y).Value = Cells(x, b).Value
x = x + 1
Loop
b = b + 1
y = y + 1
Loop
--
Regards,
Linda

" wrote:

On 19 Sep, 09:32, linda wrote:
Hi!
how to set a formula to cell using macro?i dont want to type the formula in
the particular cell since i face problem to create chart from the data.
here's the formula:
=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)=TRIM($A2))*( TRIM(Sheet1!$B$1:$B$100)=-TRIM(B$1)))
and i need to copy the formula across 50R*50C.
Thanks!
--
Regards,
Linda

Why dont you place your formula in a area away from where you want to
chart.
Then copy the result from your formula and paste special to the cell
where you want to chart from?
Maybe create your formula in another sheet in your workbook?
Does thathelp?
Mark




--

Dave Peterson