Calculation

[Guest]

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Guest]

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Guest]

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Guest]

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

What error are you getting?

And post the code that you're using.

Maybe adding a check to see if all three cells are numeric would be sufficient:

You can use =count() to test that.

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

:

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Guest]

Now it works Dave!

You just made me look good to my boss!
thank you very much!

Best regards,

Abilio Andries

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

:

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

Thank goodness!

I was running out of suggestions <vbg>.

Now it works Dave!

You just made me look good to my boss!
thank you very much!

Best regards,

Abilio Andries

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)


Abilio wrote:

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

:

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Guest]

Hey Dave,

There is a very weird thing happening to my macro, after I run the
calculations, I sort the result in descending order, and I get a bunch of
empty cells before the highest number shows up followed by the cells with
smaller results.

Thank goodness!

I was running out of suggestions <vbg>.

Now it works Dave!

You just made me look good to my boss!
thank you very much!

Best regards,

Abilio Andries

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

:

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)


Abilio wrote:

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

:

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub

 

[Dave Peterson]

Those cells aren't empty. They contain formulas that evaluate to ""--empty
strings.

You could change your formula to put them at the bottom.

...."=IF(RC[-5]=0,-999999

Then use format|conditional formatting to hide those values--but that could
upset later calculations.

I sometimes use a helper column of cells:

=if(a1="","zzzzzzzzzzzz",a1)

And sort by that column. (You'll want to adjust that string to make sure that
sorts the way you want.)

====
Or just use data|filter|autofilter and not even sort the data at all.

Hey Dave,

There is a very weird thing happening to my macro, after I run the
calculations, I sort the result in descending order, and I get a bunch of
empty cells before the highest number shows up followed by the cells with
smaller results.

Thank goodness!

I was running out of suggestions <vbg>.

Now it works Dave!

You just made me look good to my boss!
thank you very much!

Best regards,

Abilio Andries

:

Hello Dave,

There quotes were not the problem. I'm still getting the same error when
using this denominator test.

Thank you for your attention!

Abilio Andries

:

Could it be the number of double quotes?

I used:
=IF(RC[-5]=0,"""",
you posted:
=IF(RC[-5]=0,""",
(one less)


Abilio wrote:

I'm sorry Dave,

When I add the: IF(RC[-5]=0,""", I get an error that I haven't been able to
figure out.

I really appreciate your help.

Abilio Andries

:

Maybe you can check the cells that can't be empty???

.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-5]=0,"""",((RC[-4]/RC[-5])*RC[-9])*100)"

(I just checked the denominator here.)

Abilio wrote:

Thank you for the tip Dave!
But now the formula is also calculating empty cells, how do I avoid
calculating empty cells on the sub you thought me?

:

Maybe just specifying the worksheets:

Sub Projection()

with worksheets("sheet1")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

with worksheets("sheet2")
.Range("Q2:Q" & Cells(.Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"
end with

End Sub

Abilio wrote:

I have this sub that does a calculation of a sheet that I am currently in.
Now I want to direct it to calculate Sheet1 and Sheet2 no matter where I'm
located in the workbook.
Thank you very much!

Sub Projection()
Range("Q2:Q" & Cells(Rows.Count, 8).End(xlUp).Row).FormulaR1C1 = _
"=((RC[-4]/RC[-5])*RC[-9])*100"

End Sub