How do I do this condition

[Jason Bly]

I want to have cell C1 print the difference between cell A1 and B1, but
insert a prefix of "F" if the difference is less than zero and a prefix of
"C"if the difference is greater than zero.

Example 1
A1=10
B1=7
C1 should be C3.00 (I want two decimal places)

Ex. 2
A1=3
B1=15
C1 should be F12.00

Can someone please tell me how to do this. I can never seem to find what I
want in online documentation.

Jason

 

[J.E. McGimpsey]

If you just want to display these characters, choose
Format/Cells/Number/Custom and enter

C0.00;F0.00;0.00;@

in the textbox. XL formats have four fields separated by semicolons.
By default, the first is for positive numbers, the second for
negative numbers, the third for zero and the fourth for text (@
represents the text that is entered)

If you want the values to actually be stored in the cell (making the
cell Text and unusuable for further calculations):

=CHOOSE(SIGN(A1-B1)+2,"F","","C") & ABS(A1-B1)

or

=IF(A1<B1,"F",IF(A1>B1,"C",""))&ABS(A1-B1)

 

[Dan E]

JB,

you should be able to change that using the Increase Decimal and Decrease
Decimal buttons.

Dan E

 

[Rod Taylor]

Hi I need Some Help with My VBA Macro
I want to change either the last occurence of a value in a cell withen the
range or all occurences to a strickthrough font also the cell two places to
the right.
Then I want to take that same info and place it in the first empty cell in
the range then I will place some other info in the cell two to the right of
that.
The Range Name is CIC_D
The Value is CNT_A,
and the Value to go two to the right of the empty cell is cntbsht
If this requires to different subs thats ok

Next question is if their are no empty cells available then can I add A cell
to the range
hanks In Advance

 

[J.E. McGimpsey]

If I understand you correctly, here's one way:

Public Sub StrikeThroughAndCopy()
Const STRIKEALL As Boolean = True
Dim CIC_D As Range
Dim lastcell As Range
Dim cell As Range
Dim CNT_A As Long
Dim cntbsht As Long

cntbsht = 100 'just something to put in
Set CIC_D = Range("A1:A100")
If Application.CountA(CIC_D) = 0 Then
MsgBox "All cells in the range are empty"
Else
'if range full, add a row
If Not IsEmpty(CIC_D(CIC_D.Count)) Then _
Set CIC_D = CIC_D.Resize(CIC_D.Count + 1)
'find last occurrance of a value - looking up from bottom
Set lastcell = CIC_D(CIC_D.Count).End(xlUp)
CNT_A = lastcell.Value
'strike out all CNT_A's or just the last
If STRIKEALL Then
For Each cell In Range(CIC_D(1), lastcell)
If cell.Value = CNT_A Then
cell.Font.Strikethrough = True
cell.Offset(0, 2).Font.Strikethrough = True
End If
Next cell
Else
lastcell.Font.Strikethrough = True
lastcell.Font.Strikethrough = True
End If
lastcell.Offset(1, 0).Value = CNT_A
lastcell.Offset(1, 2).Value = cntbsht
End If
End Sub



Change STRIKEALL to False to strike out only the last cell

 

[Rod Taylor]

WOW This will take me awhile to decipher but thanks for the time you put
into my question
news:[email protected]...

 

[J.E. McGimpsey]

Just noticed -

the

Else
lastcell.Font.Strikethrough = True
lastcell.Font.Strikethrough = True

should be

Else
lastcell.Font.Strikethrough = True
lastcell.Offset(0, 2).Font.Strikethrough = True