cell with formula
 

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

cell with formula
 

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

cell with formula
 

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

cell with formula
 

Maybe you could just use another formula to retrieve those values from that
other range:

=if(somecell=0,na(),somecell)
like
=if(sheet99!A1=0,na(),sheet99!a1)

The na()'s won't appear on your chart, but will "clutter" up your table. You
may want to use Format|Conditional formatting to hide those errors.



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

cell with formula
 

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

cell with formula
 

I'm not quite sure what you want but I just put the formula in a3 and
recorded this. Maybe you can modify to suit.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/19/2007 by Donald B. Guillett
'

'
Selection.AutoFill Destination:=Range("A3:J3"), Type:=xlFillDefault
Range("A3:J3").Select
Selection.AutoFill Destination:=Range("A3:J22"), Type:=xlFillDefault
Range("A3:J22").Select
End Sub

modified
Sub makeformulas()
Range("b2").Formula = "=SUMPRODUCT((TRIM(Sheet1!$A$1:$A$100)" _
& "=TRIM($A2))*(TRIM(Sheet1!$B$1:$B$100)=TRIM(B$1))) "
Range("b2").AutoFill Destination:=Range("b2:J2")
Range("b2:J2").AutoFill Destination:=Range("b2:J22")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"linda" wrote in message
...
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

cell with formula
 

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

cell with formula
 

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

cell with formula
 

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

cell with formula
 

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

the answer
 

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