I have cols of data (one would be percent change) where I
would divide. I want all of the cells to display right
aligned. I also want to change the "division by zero"
result to be changed to a "-", and centered. I can do it
when I want a dollar sign or no sign...with an if statment
to change the div by zero and the accounting format.
I want to it to occur on the percent columns as well, and
can't figure out how...the "-" never comes out centered.
I think I just need a custom format but can't seem to get
it.
Please help!

This may provide some food for thought,

Column A : Contains Numerators
Column B : Contains Denominators
Column D: Contains Formula : =If(Bn < 0,An/Bn,"-") example :
=If(B7<0,A7/B7,"-")

Then the macro to try is,
-------------------------------------
Private Sub Worksheet_Calculate()

Dim iRow As Integer

For iRow = 2 To 20
If Cells(iRow, 4).Value = "-" Then
Cells(iRow, 4).HorizontalAlignment = xlCenter
Else
Cells(iRow, 4).HorizontalAlignment = xlRight
Cells(iRow, 4).NumberFormat = "#.0%"
End If
Next iRow
End Sub
------------------------------------------
I think this would be better if incorporated into a Function, but I couldn't
get that to work


"TC" wrote in message
...
I have cols of data (one would be percent change) where I
would divide. I want all of the cells to display right
aligned. I also want to change the "division by zero"
result to be changed to a "-", and centered. I can do it
when I want a dollar sign or no sign...with an if statment
to change the div by zero and the accounting format.
I want to it to occur on the percent columns as well, and
can't figure out how...the "-" never comes out centered.
I think I just need a custom format but can't seem to get
it.
Please help!

Wow! I think that is above me.
I had the column D part already.
There must be a way to do it with a format because the
accounting format does it exactly how I want it...I just
cannot get the percent to do it.
ANYBODY ELSE???

-----Original Message-----
This may provide some food for thought,

Column A : Contains Numerators
Column B : Contains Denominators
Column D: Contains Formula : =If(Bn < 0,An/Bn,"-")
example :
=If(B7<0,A7/B7,"-")

Then the macro to try is,
-------------------------------------
Private Sub Worksheet_Calculate()

Dim iRow As Integer

For iRow = 2 To 20
If Cells(iRow, 4).Value = "-" Then
Cells(iRow, 4).HorizontalAlignment = xlCenter
Else
Cells(iRow, 4).HorizontalAlignment = xlRight
Cells(iRow, 4).NumberFormat = "#.0%"
End If
Next iRow
End Sub
------------------------------------------
I think this would be better if incorporated into a
Function, but I couldn't
get that to work


"TC" wrote in message
...
I have cols of data (one would be percent change) where
I
would divide. I want all of the cells to display right
aligned. I also want to change the "division by zero"
result to be changed to a "-", and centered. I can do
it
when I want a dollar sign or no sign...with an if
statment
to change the div by zero and the accounting format.
I want to it to occur on the percent columns as well,
and
can't figure out how...the "-" never comes out centered.
I think I just need a custom format but can't seem to
get
it.
Please help!


.