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