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
  • Last Modified:

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
Asked:
surah79
1 Solution
 
VipulKadiaCommented:
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
 
Neil RussellTechnical Development LeadCommented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Another variation of Neilsr's code (NOT FOR POINTS)

    For Each CellVal In DestRng.Cells
                CellVal.Value = - ABS(CellVal.Value)
    Next
0
 
surah79Author Commented:
Excellent and thanks a lot
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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