#1
|
|||
|
|||
Excel VBA to convert number to negative
I want an entered number in a cell to be negative if there is any text in the previous cell in the row.
So if the user selects any value from the dropdown in A1, when they enter a numeric value in B1 it will convert to negative. I am able to accomplish this on a per cell basis using this code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("A1:B1"), Target) Is Nothing Then If IsNumeric(Range("B1").Value) Then Application.EnableEvents = False If Range("A1") = "" Then Range("B1") = Abs(Range("B1").Value) Else Range("B1") = -Abs(Range("B1").Value) End If Application.EnableEvents = True End If End If End Sub But I am unable to figure out how to apply it to a range of cells from A1:A50 and B1:B50 |
#2
|
|||
|
|||
Hi,
Not tested but give this a try. It should loop through the cells from row 1 to row 50. Code:
Private Sub Worksheet_Change() '(ByVal Target As Range) Dim cel As Range If Not Intersect(Range("A1:B1"), Target) Is Nothing Then If IsNumeric(Range("B1").Value) Then For Each cel In Range("A1:A50") Application.EnableEvents = False If Range("A" & cel.Row) = "" Then Range("B" & cel.Row) = Abs(Range("B" & cel.Row).Value) Else Range("B" & cel.Row) = -Abs(Range("B" & cel.Row).Value) End If Next cel Application.EnableEvents = True End If End If End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Convert a number into Hindi | Kalegola | Word | 13 | 01-10-2014 10:16 PM |
Custom formatting to make negative numbers show w/o negative sign on charts | todor | PowerPoint | 3 | 01-16-2013 03:45 AM |
convert to number | gsrikanth | Excel | 1 | 01-09-2012 01:01 AM |
Start slides from a negative number | powerpointbry | PowerPoint | 12 | 06-29-2011 12:14 AM |
Convert Number to Text | devcon | Word | 0 | 07-10-2010 01:16 AM |