Automatically Converting Value to negative

[mleblanc001]

Hi VBA Gurus,
I've been playing around with this for a little while now and getting a bitfrustrated. I've created a code which works well by pressing the commandbutton to convert a positive number in a specific range of cells to a negativenumber. I would like to take it to the next step by indicating that assoon as there is an entry made in a specific cell in a range of cells of apositive number to automatically change it to negative. If a numberwithin that range of cells is already a negative, to leave it alone. Ihope you understand what I mean. I have included a worksheet as anexample of my data.

As you can see from the worksheet, I have a script in Module1 which I use incollaboration with a command button on top of the worksheet. I also have a the same script under sheet1where I use “Private Sub Worksheet_Change(ByVal Rng As Range)” to try to havethe script automatically change positive values to negative values. When I enter numbers, the first number Ienter works as it should but as soon as I enter a second number it goes into aloop and I have to ctrl ► breakout of it.

Can anyone help with this dilemma?

Thank you

[mleblanc001]

Good morning VBA Gurus,

I found the solution to the issue I posted.

My original script which I put directly under Sheet1 VBA window (not under amodule) was as follows:


Private SubWorksheet_Change(ByVal Rng As Range)
'Dim Rng As Range
Dim myCell As Range
Set Rng =Range("e3:f23")
For Each myCell In Rng
If myCell.Value<> "" Then
IfIsNumeric(myCell.Value) Then
myCell.Value =myCell.Value * -1
End If
End If
Next myCell
Dim myCell2 As Range
Set Rng =Range("h46:h46")
For Each myCell2 InRng
If myCell2.Value<> "" Then
IfIsNumeric(myCell2.Value) Then
myCell2.Value =myCell2.Value * -1
End If
End If
Next myCell2
ActiveWorkbook.RefreshAll
End Sub

I included another ifand end if to my script and it worked

If myCell.Value > 1Then

For anyone who needs ascript like this to change entered numbers from a positive to a negative, thecomplete script is as follows:

Private SubWorksheet_Change(ByVal Rng As Range)
Dim myCell As Range
Set Rng =Range("e3:f23")
For Each myCell In Rng
If myCell.Value<> "" Then
IfIsNumeric(myCell.Value) Then
If myCell.Value > 1Then
myCell.Value =myCell.Value * -1
End If
End If
End If
Next myCell
Dim myCell2 As Range
Set Rng =Range("h46:h46")
For Each myCell2 InRng
If myCell2.Value<> "" Then
If IsNumeric(myCell2.Value)Then
myCell2.Value =myCell2.Value * -1
End If
End If
Next myCell2
ActiveWorkbook.RefreshAll
End Sub

[SamT]

Since your script runs every time there is a change anywhere on the sheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("e3:f23")) Is Nothing Or _
Intersect(Target, Range("h46:h46")) Is Nothing Then
If IsNumeric(Target) And Target > 0 Then _
Target = Target * -1
End If
End Sub

I see that you used "Rng" in the Sub declaration and as a variable inside the sub. One should not reuse names like that. VBA can get confused.

[mleblanc001]

Hi SamT,
Thank you for responding, I really appreciate the help I get in this Forum. Thank you for your comment on reusing variables and cleaning up the script. Unfortunately, the script changes all numbers entered to a negative. I just need to have the script react when there is a change in the specific range I am referring to. In other words, when a positive number is entered in range e3 to f23 and in cell h46 to change those numbers to a negative. Also, another thing I would like to do that I haven't attempted yet is make the script a little more dynamic indicating that if a row is entered, to shift the range to include that cell in column E or F and shift the cell h46 not to lose that cell in the shifting of the rows. I would really appreciate your comments on this issue. Thanks again.

[SamT]

the script changes all numbers entered to a negative. Do you mean "all numbers" on the sheet? Or "all numbers" entered anywhere on the sheet?
Because you said
as soon as there is an entry made in a specific cell in a range of cells of a positive number to automatically change it to negative.


a little more dynamic indicating that if a row is entered, to shift the range to include that cell in column E or F That's not hard at all.


shift the cell h46 not to lose that cell in the shifting of the rows. Do you mean that sometimes new data is added below Row 46?



Oops! I forgot the most important part of an Event sub...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("e3:f23")) Is Nothing Or _
Intersect(Target, Range("h46:h46")) Is Nothing Then
If IsNumeric(Target) And Target > 0 Then _
Target = Target * -1
End If

Application.EnableEvents = True
End Sub

[mleblanc001]

Sorry for not making myself clear enough. When I enter an amount in cells E3:F23 and in cell H46 only negative numbers should show there. Any other cells should show whatever sign is input (positive or negative numbers). Your script still changes all numbers in all cells in Sheet1 to negatives but I only require the script to change numbers entered in the cells E3 to F3 and H46. I've cleaned up my script by combining the ranges as shown below. Please tell me what you think. Is there a better way to represent what I am trying to do? Also, can you suggest how I can make it more dynamic when adding rows as mentioned in my previous message.


Private Sub Worksheet_Change(ByVal Rng As Range)
Dim myCell AsRange
Set Rng =Range("e3:f23,h46")
For Each myCellIn Rng
If myCell.Value<> "" Then
IfIsNumeric(myCell.Value) Then
If myCell.Value> 1 Then
myCell.Value =myCell.Value * -1
End If
End If
End If
Next myCell
End Sub


Thank you

[SamT]

OK, if you're happy, then I'm happy.

[mleblanc001]

Hi Sam T,
It seems to work but if there's a more dynamic way to write the procedure, I'm always willing to learn new techniques and ideas. I still can't figure out how to expand the procedure if someone might insert a row in the Excel sheet. Is this something that you could help me with?

Thank you and the Forum so much for all your help

[SamT]

Expanding the Range really depends on the structure and layout of the sheet.
How can you logically determine the bottom of the range now?

[mleblanc001]

Hi Sam T,
There are two bottom ranges as shown in the attached picture. One would be row 23 if a row is entered above this row and the other one would be the lone cell H46 if a row is entered above row 46. If this were to happen, then the code would shift and would shift where the negatives should go. Is there a way to add code to take any displacement of row into consideration in order for the code to shift with the displacement of rows?

Any help you can give me on this would be greatly appreciated. :-)

Thank you again for your assistance.

[SamT]

Looking at that picture, it makes no sense to move H46. That would leave the cell to the right of "Total Remittance..." empty and would replace the Total in h47 with the value of "Total Remit..."

If you think you might have to add some rows to the main table, you should move the entire summary table down so as to not destroy its structure.

In re Dynamic Ranges. Assuming you take advice and do not move H46 away from "Total Remit...",

Assuming that you will always have a label in Column A of the Main table

Dim LastEXPITM As Range
Set LastEXPITM = Cells(Rows.Count, "A").End(xlUp).Offset(0, 5)

Assuming that you might not have a label in the last Row of Column A of the main table

Dim LastEXPITM As Range
Dim JVITM As range
Set JVITM = Cells.Find("JVITM - BATCH")
Set LastEXPITM = JVITM.Offset(0, 2).End(xlUp)

Combine one of the above with

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Then Exit Sub

Dim JV_EX As Range
Dim TotalRemit As Range
Set TotalRemit = Cells(Rows.Count, "H").End(xlUp).Offset(-1,0)
'Insert one of above here '<-----------------
Set JV_EX = Range(Range("E7"), LastEXPITM)

Application.EnableEvents = False

If Target Is TotalRemit And Target > 0 Then
TotalRemit = TotalRemit * -1
ElseIf Not Intersect(Target, JV_EX) Is Nothing And Target > 0 Then
Target = Target * -1
End If

Application.EnableEvents = True
End Sub


If this doesn't work, there is something that you are not telling us.

[mleblanc001]

Good morning Sam T,
I included your script and tried it. It works for the cells above H46 meaning that if I put a (+) number it converts it to a (-) number but H46 (total remit) should also be a negative but it's not converting it to a (-) number. Maybe its the placing of the EXPITM (the first add in) that's the problem. I'll keep working on it though. If you have any suggestions, it would be greatly appreciated.

Thanks again

[SamT]

Insert the MsgBox line where indicated

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Then Exit Sub

Dim JV_EX As Range
Dim TotalRemit As Range
Set TotalRemit = Cells(Rows.Count, "H").End(xlUp).Offset(-1,0)
MsgBox TotalRemit.Address '<-------------------------------------

'Insert one of above here '<---
Set JV_EX = Range(Range("E7"), LastEXPITM)
'
'
'
'

[mleblanc001]

Hi again Sam T,
I used the MsgBox TotalRemit.Address and MsgBox Target.Address to try to pinpoint where the script was failing. I put it in front of the statement "If Target Is TotalRemit And Target >0 Then" and I put it after that statement as well to see if it was recognising that statement and it gave me a response when it was before the statement but not after the statement so that's when I realised that it was not recognising the "If Target Is TotalRemit And Target >0 Then" statement. That when I saw it...It should have said "If Target = TotalRemit And Target >0 Then" When I did that it worked. Thank you so much Sam T for leading up the right path. I really appreciate your help. I'm attaching the whole script for someone else's use.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim JV_EX As Range
Dim TotalRemit As Range
Dim LastEXPITM As Range
Set TotalRemit = Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)
Set LastEXPITM = Cells(Rows.Count, "A").End(xlUp).Offset(0, 5)
Set JV_EX = Range(Range("E7"), LastEXPITM)


Application.EnableEvents = False


If Not IsNumeric(Target) Then Exit Sub
If Target = TotalRemit And Target > 0 Then
TotalRemit = TotalRemit * -1
ElseIf Not Intersect(Target, JV_EX) Is Nothing And Target > 0 Then
Target = Target * -1
End If


Application.EnableEvents = True
End Sub

[SamT]

t was not recognising the "If Target Is TotalRemit And Target >0 Then" statement. That when I saw it...It should have said "If Target = TotalRemit And Target >0 Then" When I did that it worked.
"If Target = TotalRemit" is not safe because it is only comparing the values.

"If Not Intersect(Target, TotalRemit) is Nothing Then" checks to see if they are the same cell.

I don't know why the "Is" Function is not working for you. I use Excel 2003. "Is" compares two Obects, and Ranges are objects.

[mleblanc001]

Hi Sam T,

When I checked the result of Target and the result of TotalRemit when I was entering an amount in H46, it was giving me H46 in both objects , so I thought that if they are equal at the time I am entering a number in that cell then it would work with an equal sign. I did some tests to see if it would fail when I added some rows and it continued to work. Why would it be dangerous, I'm not sure I understand. I wouldn't want to risk a failure. I am using Excel 2016. Has the "Is" been replaced with another function?

Thank you again Sam T for the warning.

[SamT]

Why would it be dangerous, I'm not sure I understand. I wouldn't want to risk a failure

Supposed Total Remittance is some number, say -42 and you change some other cell on the sheet to -42, then the Target, (the Other cell that is -42,) equals TotalRemit. Bang! TotalRemit becomes -42 * -1

[SamT]

Now that we have your sheet working the way you want. I would like to introduce you to some advanced Programming Practices.

Any Event Sub can only have one instance, so you cannot have, say, one Change Event for H46 and a different Change Event sub for another Range. IOW, the one Event sub must handle that Event for all Ranges, no matter if all Ranges get a different treatment.

I have seen may Event subs so overloaded that one could not decipher what was going on and the Writer could not make the Sub works properly. One solution for that is to write Event Subs so that their only task is to determine which Range the Event occurred at.

For example

Private Sub Worksheet_Change(ByVal Target As Range)
If Target is (A1) THen MakeNegative Target
ElseIf: Target is A2 then MakePositive Target
EsleIf: Target is A3 THen MakeRed Target
ElseIf: Tegrat is A4 then MakeBlue Target
End IF
End Sub As you can see, the Event sub is neat, concise, easily edited and easy to make right. If there is any problem it will be in a separate and simple Sub or Function that only does one task.

Refactoring your code to use this Pattern does seem a bit more complicate and bulky, but it also means you can add to the actual Event sub with no issues of code interference.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, JV_EX) Is Nothing then MakeNegative Target
ElseIf: Not Intersect(Target, TotalRemit) Is Nothing then MakeNegative Target
'ElseIf: Some Other Range... Do something different

End If
Application.EnableEvents = True 'Redundant, but Just In Case
'all subs that do something to the worksheet should set and reset EnableEvents
End Sub


Private Function JV_EX() As Range
'Note: By setting the ranges for the Event sub in separate Functions, If you ever
'modify the Worksheet, you only have to edit the Set Ranges Functions. It also keeps
'the Event Sub neat and concise.

Set JV_EX = Range(Range("E7"), Cells(Rows.Count, "A").End(xlUp).Offset(0, 5))
End Function


Private Function TotalRemit() As Range
Set TotalRemit= Cells(Rows.Count, "H").End(xlUp).Offset(-1, 0)
End Function


Private Sub MakeNegative(Target As Range)
If Not IsNumeric(Target) Or Target <=0 Then Exit Sub

Application.EnableEvents = False
Target = Target * -1
Application.EnableEvents = True
End Sub

[mleblanc001]

Yep, I see and understand what your doing, your calling these subs and functions keeping them separate and have a main program. I've been educated in multiple programming languages (C++, DBase, Cobol) and I understand Object-Oriented programming. You are absolutely right, it does keep it neat and easier to debug. I had taken Visual Basic at school many many years ago but hadn't kept it up. Then when Excel VBA became available, I started to play with it a bit but my job didn't require me knowing VBA but I used it whenever I could...very powerful but not many people or organisation use it. I want to thank you for taking the time to help me out. I truly appreciate it.