ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Converting all numbers to negative (https://www.excelbanter.com/excel-programming/379425-re-converting-all-numbers-negative.html)

Bernie Deitrick
Converting all numbers to negative
 
If you have numbers already in a range, select the range and run this macro

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Does anyone know how to create a code that would convert all numbers entered
into a range of cells into a negative number? -ABS Thanks!



Bernie Deitrick
Converting all numbers to negative
 
James,

No, it isn't correct. The Me is not an allowed keyword in this context. And you left out the
looping.

Put this code into a regular codemodule:

Sub TryNow()
Dim myCell As Range
Dim myRng As Range

Set myRng = Range("J8:AA22")
For Each myCell In myRng
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, is this right?

Sub TryNow()
Dim myCell As Range
Dim myRng As Range
Set myRng = Me.Range("J8:AA22")
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

"Bernie Deitrick" wrote:

If you have numbers already in a range, select the range and run this macro

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Does anyone know how to create a code that would convert all numbers entered
into a range of cells into a negative number? -ABS Thanks!

Bernie Deitrick
Converting all numbers to negative
 
James,

Copy the code below, right-click on your sheet tab, and select "View Code" Then paste the code into
the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Me.Range("J8:AA22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Me.Range("J8:AA22"))
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
Application.EnableEvents = True
End Sub



"Please help James" wrote in message
...
Bernie, the code does work...how do I make it run automatically? So that when
a user enters a number it automatically converts it to a negative.

"Bernie Deitrick" wrote:

James,

No, it isn't correct. The Me is not an allowed keyword in this context. And you left out the
looping.

Put this code into a regular codemodule:

Sub TryNow()
Dim myCell As Range
Dim myRng As Range

Set myRng = Range("J8:AA22")
For Each myCell In myRng
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, is this right?

Sub TryNow()
Dim myCell As Range
Dim myRng As Range
Set myRng = Me.Range("J8:AA22")
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

"Bernie Deitrick" wrote:

If you have numbers already in a range, select the range and run this macro

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Does anyone know how to create a code that would convert all numbers entered
into a range of cells into a negative number? -ABS Thanks!

Bernie Deitrick
Converting all numbers to negative
 
Change

For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
End If
Next myCell


to

For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
Else
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

This assumes that you don't have any strings in the range...

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, I already have code written in that window. Where should I put your
code?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("E8:AA22")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub

"Bernie Deitrick" wrote:

James,

Copy the code below, right-click on your sheet tab, and select "View Code" Then paste the code
into
the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Me.Range("J8:AA22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Me.Range("J8:AA22"))
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
Application.EnableEvents = True
End Sub



"Please help James" wrote in message
...
Bernie, the code does work...how do I make it run automatically? So that when
a user enters a number it automatically converts it to a negative.

"Bernie Deitrick" wrote:

James,

No, it isn't correct. The Me is not an allowed keyword in this context. And you left out the
looping.

Put this code into a regular codemodule:

Sub TryNow()
Dim myCell As Range
Dim myRng As Range

Set myRng = Range("J8:AA22")
For Each myCell In myRng
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, is this right?

Sub TryNow()
Dim myCell As Range
Dim myRng As Range
Set myRng = Me.Range("J8:AA22")
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

"Bernie Deitrick" wrote:

If you have numbers already in a range, select the range and run this macro

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Does anyone know how to create a code that would convert all numbers entered
into a range of cells into a negative number? -ABS Thanks!

Bernie Deitrick
Converting all numbers to negative
 
James,

Code like this:

Set myRng = Me.Range("E8:AA22")

Application.EnableEvents = False

For Each mycell In Intersect(Target, myRng).Cells

If myCell.Value = "" Then
myCell.Value = 0
End If

If Not Intersect(mycell, Me.Range("E8:E22")) Is Nothing Then
mycell.Value = Abs(mycell.Value)
End If

If Not Intersect(mycell, Me.Range("J8:AA22")) Is Nothing Then
mycell.Value = -Abs(mycell.Value)
End If

Next mycell

Application.EnableEvents = True


HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
That sort of worked, the only problem is that E8:E22 should be positive (ABS)
and J8:AA22 should be negative (-ABS). Any suggestions?

"Bernie Deitrick" wrote:

Change

For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
End If
Next myCell


to

For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
Else
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

This assumes that you don't have any strings in the range...

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, I already have code written in that window. Where should I put your
code?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("E8:AA22")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
For Each myCell In Intersect(Target, myRng).Cells
If myCell.Value = "" Then
myCell.Value = 0
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0
End Sub

"Bernie Deitrick" wrote:

James,

Copy the code below, right-click on your sheet tab, and select "View Code" Then paste the
code
into
the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Me.Range("J8:AA22")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Me.Range("J8:AA22"))
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
Application.EnableEvents = True
End Sub



"Please help James" wrote in message
...
Bernie, the code does work...how do I make it run automatically? So that when
a user enters a number it automatically converts it to a negative.

"Bernie Deitrick" wrote:

James,

No, it isn't correct. The Me is not an allowed keyword in this context. And you left out
the
looping.

Put this code into a regular codemodule:

Sub TryNow()
Dim myCell As Range
Dim myRng As Range

Set myRng = Range("J8:AA22")
For Each myCell In myRng
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Bernie, is this right?

Sub TryNow()
Dim myCell As Range
Dim myRng As Range
Set myRng = Me.Range("J8:AA22")
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

"Bernie Deitrick" wrote:

If you have numbers already in a range, select the range and run this macro

Sub TryNow()
Dim myCell As Range
For Each myCell In Selection
If IsNumeric(myCell.Value) And (Not IsEmpty(myCell)) Then
myCell.Value = -Abs(myCell.Value)
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Please help James" wrote in message
...
Does anyone know how to create a code that would convert all numbers entered
into a range of cells into a negative number? -ABS Thanks!

Bernie Deitrick
Converting all numbers to negative
 
James,

You're quite welcome. Glad to hear that you got it to work....

Bernie
MS Excel MVP

Bernie, you are the king of xls code!! You rock, Thank You! it worked!


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com