Macro to Convert all the data in column in Negative Value

[Chandresh]

HI All ,

I have written below code for converting all the numbers in the column to negative value however if some text comes in between my macro is not running can any one help me also the macro should run in all the sheets in that workbook.


Sub Cnegative()
'
' Cnegative Macro
'
For Each cell In Range("e3:e5000")
If cell.Value > 0 Then
cell.Value = cell.Value * -1
End If
Next cell
End Sub

 

[Chandresh]

I used below code still its running on single sheet

[Sub TempLoopforall()
On Error Resume Next
For Each cell In Union(Range("C3:C250000"), Range("E3:E250000"), Range("Q3:Q250000"), Range("S3:s250000")).SpecialCells(xlCellTypeConstants, xlNumbers)
If cell.Value > 0 Then cell.Value = -cell.Value
Next cell
End Sub][/CODE]

 

[Yongle]

You did not apply the method to loop sheets provided in post 7
Try

Sub Test4()
    Dim ws As Worksheet, cell As Range, rng As Range
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set rng = Union(ws.Range("C3:C250000"), ws.Range("E3:E250000"), ws.Range("Q3:Q250000"), ws.Range("S3:s250000")).SpecialCells(xlCellTypeConstants, xlNumbers)
            For Each cell In rng
                If cell.Value > 0 Then cell.Value = -cell.Value
            Next cell
        On Error GoTo 0
        Set rng = Nothing
    Next ws
End Sub

 

[Chandresh]

Thanks I will try

 

[Peter_SSs]

For Each cell In Range("C3:C250000,E3:E250000,Q3:Q250000,S3:s250000").SpecialCells(xlCellTypeConstants, xlNumbers)
If Icell.Value > 0 Then cell.Value = -cell.Value
Next cell

If you have anything like that number of rows and many of the relevant cells are numeric & several worksheets, you might find this code considerably faster.

Test in a copy of your workbook.

Sub ConvertToNegative()
  Dim a As Variant, aCol As Variant
  Dim i As Long
  Dim ws As Worksheet

  Const sCols As String = "C E Q S"
  
  For Each ws In Worksheets
    For Each aCol In Split(sCols)
      a = ws.Range(aCol & 3, ws.Cells(ws.Rows.Count, aCol).End(xlUp)).Value
      For i = 1 To UBound(a)
        If IsNumeric(a(i, 1)) Then
          If a(i, 1) > 0 Then a(i, 1) = -a(i, 1)
        End If
      Next i
      ws.Range(aCol & 3).Resize(UBound(a)).Value = a
    Next aCol
  Next ws
End Sub