Solved

# Excel Change the format in The column

Posted on 2011-10-31
139 Views
Hi Guys

I am trying to convert the number in the column C,D, And E to become negative number after macro is run .
Trial.xls
0
Question by:surah79

LVL 8

Expert Comment

Write following code in your macro :
Sub MakeNegativeNumbers()
Dim ShObj As Worksheet
Dim DestRng As Range
Dim CellVal As Range

Set ShObj = ThisWorkbook.Worksheets(1)
Set DestRng = ShObj.Range(ShObj.Cells(2, 3), ShObj.Cells(3, 5))

For Each CellVal In DestRng.Cells
CellVal.Value = CellVal.Value * -1
Next
End Sub

Also see attached file. Trial-1-.xls
0

LVL 37

Accepted Solution

Thats fine IF columns C,D and E will NEVER EVER contain a negative number to start with..... IF they could you need.

Sub MakeNegativeNumbers()
Dim ShObj As Worksheet
Dim DestRng As Range
Dim CellVal As Range

Set ShObj = ThisWorkbook.Worksheets(1)
Set DestRng = ShObj.Range(ShObj.Cells(2, 3), ShObj.Cells(3, 5))

For Each CellVal In DestRng.Cells
If (Cellval.value > 0 then
CellVal.Value = CellVal.Value * -1
End if
Next
End Sub

0

LVL 43

Expert Comment

Or this

Sub negate()
Dim cc As Range
Set cc = Range("A1").SpecialCells(xlCellTypeLastCell).Offset(1, 0)
cc.Value = -1
cc.Copy
Range("c2:e3").PasteSpecial Paste:=xlPasteValues, operation:=xlPasteSpecialOperationMultiply
cc.ClearContents
End Sub
0

LVL 43

Expert Comment

Another variation of Neilsr's code (NOT FOR POINTS)

For Each CellVal In DestRng.Cells
CellVal.Value = - ABS(CellVal.Value)
Next
0

Author Closing Comment

Excellent and thanks a lot
0