| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formulas
I have this formula that calculates correctly the sheet1 but for some reason
it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") ..Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") ..Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas
I guess you will have to put a dot (".") before Cells!
"Abilio" wrote: I have this formula that calculates correctly the sheet1 but for some reason it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas
do You mean after & ? if so, It didn't work.
"Martin Krastev" wrote: I guess you will have to put a dot (".") before Cells! "Abilio" wrote: I have this formula that calculates correctly the sheet1 but for some reason it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas
Function Projection() 'This step calculates projections, it should be
'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") ..Range("Q2:Q" & .Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") ..Range("Q2:Q" & .Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function "Abilio" wrote: do You mean after & ? if so, It didn't work. "Martin Krastev" wrote: I guess you will have to put a dot (".") before Cells! "Abilio" wrote: I have this formula that calculates correctly the sheet1 but for some reason it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas
Try making this a sub and not a function.
"Abilio" wrote: I have this formula that calculates correctly the sheet1 but for some reason it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formulas
How do I do it?
"Duke Carey" wrote: Try making this a sub and not a function. "Abilio" wrote: I have this formula that calculates correctly the sheet1 but for some reason it doesn't calculates the sheet2. I'd appreciate the help. Function Projection() 'This step calculates projections, it should be 'done in both of the Sheet1 and Sheet2. With Worksheets("Sheet1") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With With Worksheets("Sheet2") .Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _ "=IF(RC[-5]=0,-999999,(RC[-4]/(RC[-5]*RC[-9]))*13300)" End With End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
| Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
| Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
| CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
| AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming | |||
Hybrid Mode
