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

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

"Dave Peterson" wrote:

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

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?

"Dave Peterson" wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

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.

Abilio wrote:

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Now it works Dave!

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

Best regards,

Abilio Andries

"Abilio" wrote:

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

Thank goodness!

I was running out of suggestions <vbg.

Abilio wrote:

Now it works Dave!

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

Best regards,

Abilio Andries

"Abilio" wrote:

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

Thank goodness!

I was running out of suggestions <vbg.

Abilio wrote:

Now it works Dave!

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

Best regards,

Abilio Andries

"Abilio" wrote:

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

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.


Abilio wrote:

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.

"Dave Peterson" wrote:

Thank goodness!

I was running out of suggestions <vbg.

Abilio wrote:

Now it works Dave!

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

Best regards,

Abilio Andries

"Abilio" wrote:

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

"Dave Peterson" wrote:

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

"Dave Peterson" wrote:

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?

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson