Excel VBA to convert number to negative

dakm63 · #1 05-08-2015, 02:53 PM

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

charlesdh · #2 05-26-2015, 10:49 AM

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