change minus values by replace plus value into column and become zero

Ali M

Active Member
Joined
Oct 10, 2021
Messages
287
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
hi
I have some minus values in column BALANCE (H) what I want adding the minus value by changing into plus for column BUYING(F) ,then it will become zero in column BALANCE (H)

COLOR2.xlsm
ABCDEFGHI
1ITEMSSTRTTRMMRNNRBUYINGSELLINGBALANCE
28/7/2021SLFR-100FOOD-1PR100NRIT125.0027.00-2.00
38/8/2021SLFR-101FOOD-2PR101NRIT230.0020.0010.00
48/9/2021SLFR-102FOOD-3PR102NRIT335.0023.0012.00
58/10/2021SLFR-103FOOD-4PR103NRIT430.0025.005.00
68/11/2021SLFR-104FOOD-5PR104NRIT540.0035.005.00
78/12/2021SLFR-105FOOD-6PR105NRIT645.0010.0035.00
88/13/2021SLFR-106FOOD-7PR106NRIT745.0020.0025.00
98/14/2021SLFR-107FOOD-8PR107NRIT855.0065.00-10.00
108/15/2021SLFR-108FOOD-9PR108NRIT960.005.0055.00
118/16/2021SLFR-109FOOD-10PR109NRIT1070.0010.0060.00
128/17/2021SLFR-110FOOD-11PR110NRIT1180.0020.0060.00
138/18/2021SLFR-111FOOD-12PR111NRIT1285.0025.0060.00
148/19/2021SLFR-112FOOD-13PR112NRIT1390.0020.0070.00
158/23/2021SLFR-116FOOD-17PR116NRIT1740.0010.0030.00
168/24/2021SLFR-117FOOD-18PR117NRIT1850.0070.00-20.00
178/26/2021SLFR-119FOOD-20PR119NRIT2045.005.0040.00
188/27/2021SLFR-115FOOD-16PR116NRIT1720.005.0015.00
198/28/2021SLFR-116FOOD-17PR117NRIT1835.0010.0025.00
208/30/2021SLFR-118FOOD-19PR119NRIT2045.0010.0035.00
218/31/2021SLFR-119FOOD-20PR120NRIT2125.0020.005.00
229/1/2021SLFR-120FOOD-21PR121NRIT2220.0010.0010.00
sheet1
Cell Formulas
RangeFormula
H2:H22H2=F2-G2



the result see the column BUYING(F) how add the mius value from column BALANCE(H) after change into puls and how become zero in column BALANCE(H)
COLOR2.xlsm
ABCDEFGH
1ITEMSSTRTTRMMRNNRBUYINGSELLINGBALANCE
28/7/2021SLFR-100FOOD-1PR100NRIT127.0027.000.00
38/8/2021SLFR-101FOOD-2PR101NRIT230.0020.0010.00
48/9/2021SLFR-102FOOD-3PR102NRIT335.0023.0012.00
58/10/2021SLFR-103FOOD-4PR103NRIT430.0025.005.00
68/11/2021SLFR-104FOOD-5PR104NRIT540.0035.005.00
78/12/2021SLFR-105FOOD-6PR105NRIT645.0010.0035.00
88/13/2021SLFR-106FOOD-7PR106NRIT745.0020.0025.00
98/14/2021SLFR-107FOOD-8PR107NRIT865.0065.000.00
108/15/2021SLFR-108FOOD-9PR108NRIT960.005.0055.00
118/16/2021SLFR-109FOOD-10PR109NRIT1070.0010.0060.00
128/17/2021SLFR-110FOOD-11PR110NRIT1180.0020.0060.00
138/18/2021SLFR-111FOOD-12PR111NRIT1285.0025.0060.00
148/19/2021SLFR-112FOOD-13PR112NRIT1390.0020.0070.00
158/23/2021SLFR-116FOOD-17PR116NRIT1740.0010.0030.00
168/24/2021SLFR-117FOOD-18PR117NRIT1870.0070.000.00
178/26/2021SLFR-119FOOD-20PR119NRIT2045.005.0040.00
188/27/2021SLFR-115FOOD-16PR116NRIT1720.005.0015.00
198/28/2021SLFR-116FOOD-17PR117NRIT1835.0010.0025.00
208/30/2021SLFR-118FOOD-19PR119NRIT2045.0010.0035.00
218/31/2021SLFR-119FOOD-20PR120NRIT2125.0020.005.00
229/1/2021SLFR-120FOOD-21PR121NRIT2220.0010.0010.00
sheet1
Cell Formulas
RangeFormula
H2:H22H2=F2-G2


as you see the highlighted cells how changes . for instance in row 2 . -2 in column H should be 2 when add to column F , then =2+25=27 and the column H become 0and so on for any minus values should calculate with this way .
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this code:
VBA Code:
Sub MyReplaceValues()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column H with data
    lr = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all rows
    For r = 2 To lr
'       Check to see if value in column H is negative
        If Cells(r, "H").Value < 0 Then
'           Add value to column F
            Cells(r, "F").Value = Cells(r, "F").Value - Cells(r, "H").Value
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
truly I don't think can be possible by vba , but you did it.
this is fantastic !
many thanks for your solution (y)
 
Upvote 0
You are welcome!
Most things are actually possible by VBA, as long as you can come up with solid logic logic on which you can program to.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top