cell with formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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
 
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
 
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.
 
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
 
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
 
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?
 
Sorry, not that I know.
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?
Click to expand...
 
Pls always post the final solution for the archives

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
linda said:
its ok,thanks anyway=)
i think i've got it
Click to expand...
 
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=)
 
Back
Top