Displaying formulae changes formatting

[SueJB]

Hi all,

Can anyone help with a strange quirk? I have a spreadsheet with a series of
numbers which have to be formatted to 1 decimal place. These show fine in
"normal" mode.

If I display formulae, some of the cells not containing formulae lose their
formatting - numbers with a decimal part are still shown to one place (eg
1.6) but those without lose the decimal (eg 1 not 1.0). All attempts to
change the formatting fail.

I need to be able to print the spreadsheet with formulae but also with
values displayed to one d.p. and I'm currently beaten.

Can anyone advise?

Thanks
SueJB

 

[macropod]

Hi Sue,

The following macro adds the formulae to the comments for each selected
cell, or even the whole worksheet, and displays the comments in an
appropriately-sized comment box. This should allow you to both display the
formulae and their results. Depending on what you're trying to achieve, and
how crowded your worksheet is, you may still need to do a bit of
reformatting, and maybe get the macro the position the comments differently,
but showing/hiding the formulae will then be as easy as toggling the comment
display on/off.

Sub AddFormulaToComment()
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no
comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
Set CommentRange = Range(Selection.Address)
End If
'If the cell contains a formula, make it a comment.
For Each TargetCell In CommentRange
With TargetCell
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
.Comment.Delete
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
With .Comment.Shape
'automatically resizes the comment
.TextFrame.AutoSize = True
'position the comment adjacent to its cell
If TargetCell.Column < 254 Then .IncrementLeft -11.25
If TargetCell.Row <> 1 Then .IncrementTop 8.25
End With
End If
End With
Next
MsgBox " To print the comments, choose" & vbCrLf & _
" File|Page Setup|Sheet|Comments," & vbCrLf & _
"then choose the required print option.", vbOKOnly
End Sub

Cheers

 

[SueJB]

Hi macropod

Many thanks for replying. This is certainly an interesting way of resolving
the problem, but unfortunately the spreadsheet must be printed with the
formulae in the relevant cells and the values in the non-formula cells
showing to one decimal place.

Thanks for your suggestion, though, much appreciated.

SueJB

 

[macropod]

Hi Sue,

When Excel's displaying formulae, it can't display both the formula and
result for a given cell (at least with Excel 2000 and earlier). Any cells
with values are simply displayed as per the raw data (eg dates get converted
to numbers, decimal values get truncated at the last non-zero value, etc.).

With the macro I posted, you can display both the formula and it's result,
without compromising any of the value formatting. If you want to have both
the formula and its result appear in the same cell, you could increase the
row height, and re-position the comment in code to put it within the cell
and above/below the value, or you could increase the column width and put
the formula beside the result. If you don't want the formula's result to
show, re-position the comment in code to put it over the result.
repositioning the comment is managed by changing the values in the lines:
If TargetCell.Column < 254 Then .IncrementLeft -11.25
If TargetCell.Row <> 1 Then .IncrementTop 8.25

Cheers

 

[SueJB]

Aah, I see ... many thanks, I'll try this.

Best wishes
SueJB