Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 150

# Excel Change the format in The column

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
surah79
1 Solution

Commented:
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

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

EngineerCommented:
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

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

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

Author Commented:
Excellent and thanks a lot
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.