Excel automatically move negative value to another cell

Hello,

Is it possible to create an Excel VBA macro to move a negative value in one cell to another cell. For example, if a negative value exists in cell A1, move it to B1.

Ideally the macro would move the value as soon as it is entered into a cell. So if a negative number is typed in A1, it would be moved to B1 automatically and immediately.

I would like to say thank you in advance for any assistance.
justinjmarshallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ess KayEntrapenuerCommented:
try somehting like this, it loops through rows in column A, if the value is less than zero, it moves to next column

Sub tested()

Dim rng As Range
Dim lrow As Integer

With ActiveSheet
lrow = .Range("A" & Rows.Count).End(xlUp).Row

For Each rng In .Range("A2:A" & lrow)

If rng.Value < 0 Then

rng.Offset(0, 1).Value = rng.Value
rng.Value = ""
End If

Next rng

End With

End Sub

Open in new window

0
justinjmarshallAuthor Commented:
Hello esskayb2d,

Thank you. I was able to get the macro to work, but only when I actually press the play button in the VBA editor. Is there any way to have the macro run constantly and run on event when a value is entered into a cell?
0
Glenn RayExcel VBA DeveloperCommented:
You could change this to a Worksheet_Change event.  The code, with some modification, would be inserted in the worksheet object - rather than a module.

Option Explicit
Dim rng As Range
Dim cl As Object
Dim lngRow As Long
Dim boolMove As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    If boolMove Then Exit Sub
    lngRow = Cells.SpecialCells(xlLastCell).Row
    Set rng = Range("A2:A" & lngRow)
    For Each cl In rng
        If cl.Value < 0 Then
            boolMove = True
            cl.Offset(0, 1).Value = cl.Value
            cl.Value = ""
        End If
    Next cl
    boolMove = False
End Sub

Open in new window


Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Ess KayEntrapenuerCommented:
I could direct you straight to microsoft
https://support.microsoft.com/en-us/kb/213612

Just replace the inside of the code with the code i provided earlier

To create the Visual Basic macro:
Right-click the Sheet1 tab and then click View Code.

The module sheet behind Sheet1 is opened.
Type the following code into the module sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("A1:C10")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
        MsgBox "Cell " & Target.Address & " has changed."
       
    End If
End Sub
                              
Click Close and Return to Microsoft Excel on the File menu.
When you type an entry in cells A1:C10 on Sheet1, a message box is displayed.
0
justinjmarshallAuthor Commented:
I'm getting a run time error with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim lrow As Integer

With ActiveSheet
lrow = .Range("A" & Rows.Count).End(xlUp).Row

For Each rng In .Range("A2:A" & lrow)

If rng.Value < 0 Then

rng.Offset(0, 1).Value = rng.Value
rng.Value = ""
End If

Next rng

End With
End Sub

Open in new window

0
justinjmarshallAuthor Commented:
0
Glenn RayExcel VBA DeveloperCommented:
The error is resulting from an overflow condition because the Worksheet_Change event is calling itself after line 12 is executed.

That's why my modified code has a Boolean check (boolMove) to ensure that it isn't recursively called in this manner.

Regards,
-Glenn
0
justinjmarshallAuthor Commented:
This was the correct solution. Error on my part prevented my from realizing that this was correct and upon correcting my mistake, this solution worked.
0
Glenn RayExcel VBA DeveloperCommented:
I was glad to help.  Worksheet_Change events require very careful review when they, in turn, change the worksheet object.

Regards,
-Glenn
0
Rob HensonFinance AnalystCommented:
You could also set Data Validation on the columns to only allow input of positive numbers in column A and only negative values in column B.

Thanks
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.