this is the macro that i get after combine all of your suggestion:

Sub Formula()

'insert CustMod formula
b = 2
Do While Cells(1, b).Value < ""
a = 2
Do While Cells(a, 1).Value < ""
Range(Cells(a, b), Cells(a, b)).Select
ActiveCell.FormulaR1C1 = _

"=IF(OR(R1C="""",RC1=""""),NA(),SUMPRODUCT((filter _raw_data!R1C2:R1500C2=RC1)*(filter_raw_data!R1C14 :R1500C14=R1C)))"
a = a + 1
Loop
b = b + 1
Loop

End Sub

this macro will fill in data for available column header only.since i've
spend 50R*50C for the table but then the actual size of table is vary from
time to time.so,it will cause problem when i need to create stacked bar chart.
therefore,i'm using macro to fill in the formula for current available data
so that it cause zero problem when creating chart.
hope this can help others too=)
--
Regards,
Linda


"Don Guillett" wrote:

Pls always post the final solution for the archives

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"linda" wrote in message
...
its ok,thanks anyway=)
i think i've got it
--
Regards,
Linda


"Dave Peterson" wrote:

Sorry, not that I know.

linda wrote:

thanks for reply Dave,
but i already tried both of your suggestion and both dont work.na()
still
include in charts.and if i copy & replace,its still charting the empty
cell
that include in the entire range.
thats why i have to copy the value from cell to cell,but then its
running
too slow.
is there any other ways?
--
Regards,
Linda

"Dave Peterson" wrote:

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


--

Dave Peterson