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!!!!
