| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
| adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
| Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
| Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Hybrid Mode
