Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-08-2015, 02:53 PM
dakm63 dakm63 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: May 2015
Posts: 1
dakm63 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-26-2015, 10:49 AM
charlesdh charlesdh is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 07:57 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft