Translate to Named Range

[joelle]

Hello Everyone,

I got great help from experts in this board for the codes below. They work beautifully.
However, it was my poor memoire to forget to mention that I needed to group multiple field for "credit" (here, A1:A2) to a named range "credit" so that:
1. When people type in a number in one field or both field of the range, each number will turn to a negative value for the "Total" to calculate the credit correctly.
2. When people add lines to my ws, the codes still work properly.

Please any edition is very much appreciated.
Nee


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric([a1]) Then [a1] = -Abs([a1])
If IsNumeric([a2]) Then [a2] = -Abs([a2])
Application.EnableEvents = True
End Sub

[malik641]

For your named range "credit", I think this will work.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
Dim cell As Variant
For Each cell In Range("credit")
If IsNumeric(cell) Then cell.Value = -Abs(cell)
Next cell
Application.EnableEvents = True
End Sub

Although this may tend to get slow with a LOT of values...lemme see if I can change this a bit...http://vbaexpress.com/forum/images/smilies/think.gif

Edit: Not too bad with a lot of values actually, but I did notice that if you cleared all the values of the range "credit", you get a debugging error. I decided to place the "On Error Resume Next" line right before the "Application.EnableEvents = False" line. This should be good now.

Hope this helps http://vbaexpress.com/forum/images/smilies/023.gif

[joelle]

Hello Joseph,

Thanks for looking at this and I'll be awaiting for your revised codes.

The set of codes I posted owes its credit to Johnske. They work beautifully for each individual cells as I would like them to from my original post.

Johnske: Not that I failed to pair your name with your great codes! I just did not know if it was appropriate (or if I had your okay) to do so. Either way, pls do not get mad at me http://vbaexpress.com/forum/images/smilies/sad2.gif.

Nee