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

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
>
>

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
> >
> >

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
> > >
> > >

It is still deleting the sheet2 and gives no results

"Martin Krastev" wrote:

> 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
> > > >
> > > >

Do I understand you correctly that you want to put the formula
=IF(L2=0,-999999,M2/L2*H2*13300)
in cell Q2 (and below until the # of rows in col h) of both sheets 1 and 2.

Or you want the formula on sheet2 to refer to cells in sheet1?

"Abilio" wrote:

> It is still deleting the sheet2 and gives no results
>
> "Martin Krastev" wrote:
>
> > 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
> > > > >
> > > > >

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
>
>

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
> >
> >