MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Why does this Macro chop off the first number in the cell? It works fine if it is a formula though.


Posted by Tim Johnson on December 28, 2001 8:01 AM

Sub NEGATIVE2()
'
' NEGATIVE2 Macro
' Macro recorded 12/28/01 by More Beer!!!
'

'

Dim CellContents As String

For Each Cell In Selection

CellContents = Right(Cell.FormulaR1C1, Len(Cell.FormulaR1C1) - 1)
Cell.FormulaR1C1 = "=(" & CellContents & ")*-1 "
CellContents = ""

Next Cell


End Sub


Posted by Gary Bailey on December 28, 2001 8:47 AM

The

Right(Cell.FormulaR1C1, Len(Cell.FormulaR1C1) - 1)

bit takes the cell contents and removes the first character. Because if the cell contains a formula then the first character will always be a "=".

However if its a value then there is no "=" so the first digit gets removed. You could change it to

If Cell.HasFormula then
CellContents = Right(Cell.FormulaR1C1, Len(Cell.FormulaR1C1) - 1)
else
CellContents = Right(Cell.FormulaR1C1, Len(Cell.FormulaR1C1) )
end if

Gary

Posted by Tim Johnson on January 02, 2002 12:07 PM

THANK YOU SIR!!!!