Please help James
12/14/2006 6:53:00 PM
Bernie, you are the king of xls code!! You rock, Thank You! it worked!
"Bernie Deitrick" wrote:
> 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" <PleasehelpJames@discussions.microsoft.com> wrote in message
> news:C87C8AED-482E-4EDF-BECB-D99B669186C8@microsoft.com...
> > 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" <PleasehelpJames@discussions.microsoft.com> wrote in message
> >> news:120F76C9-E475-4226-BE64-1AAB052CFA55@microsoft.com...
> >> > 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" <PleasehelpJames@discussions.microsoft.com> wrote in message
> >> >> news:2E2C44F9-BE6A-4435-9333-D267867307F2@microsoft.com...
> >> >> > 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" <PleasehelpJames@discussions.microsoft.com> wrote in message
> >> >> >> news:DA228791-A1C1-4769-9FE5-2E9217C880BC@microsoft.com...
> >> >> >> > 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" <PleasehelpJames@discussions.microsoft.com> wrote in message
> >> >> >> >> news:37679E35-9EAA-429C-8DE9-A6FBF0016EDA@microsoft.com...
> >> >> >> >> > 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!
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>