Changing values from neg to pos based off other cell with text

msohare91

New Member
Joined
Jun 8, 2020
Messages
17
Office Version
  1. 2016
Hi all,

I'm trying to change the value of cells in another column based off a first column which has specific text.

If cells in Column H have "Buy" I want the matching cell in Column I to turn the value negative. I know I can do a simple excel formula for this but as this will be in the middle of larger code I need it to be vba and am finding it surprisingly tricky

I have tried lots of different methods but keep getting errors. I have tried Else-If function and loops but can't get them to work. Running into issues with setting objects too. Would the formatting of the cell affect the output? I.e would the cells have to be formatted to Number for the *-1 in the code to work?

Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Maybe something like this:
VBA Code:
Sub MyCode()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last cell in column H with data
    lr = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all row in column H, starting with row 2
    For r = 2 To lr
'       Multiply column I by -1 if column H is "Buy"
        If Cells(r, "H") = "Buy" Then Cells(r, "I") = cell(r, "I") * -1
    End If
        
    Application.ScreenUpdating = True
        
End Sub
 

msohare91

New Member
Joined
Jun 8, 2020
Messages
17
Office Version
  1. 2016
Maybe something like this:
VBA Code:
Sub MyCode()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last cell in column H with data
    lr = Cells(Rows.Count, "H").End(xlUp).Row
   
'   Loop through all row in column H, starting with row 2
    For r = 2 To lr
'       Multiply column I by -1 if column H is "Buy"
        If Cells(r, "H") = "Buy" Then Cells(r, "I") = cell(r, "I") * -1
    End If
       
    Application.ScreenUpdating = True
       
End Sub
Hi. Thanks this looks good. However it's giving me the error 'End If without Block If'. Your If statement looks fine to me though..
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Sorry, few typos. It should be:
VBA Code:
Sub MyCode()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last cell in column H with data
    lr = Cells(Rows.Count, "H").End(xlUp).Row
    
'   Loop through all row in column H, starting with row 2
    For r = 2 To lr
'       Multiply column I by -1 if column H is "Buy"
        If Cells(r, "H") = "Buy" Then Cells(r, "I") = Cells(r, "I") * -1
    Next r
        
    Application.ScreenUpdating = True
        
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Also do I need the second Cells(r,"I") before the *-1? It seems like it would run without this.
You absolutely need it. Otherwise, you would be setting it equal to -1 instead of multiplying the cell by -1.
 

msohare91

New Member
Joined
Jun 8, 2020
Messages
17
Office Version
  1. 2016
Sorry, few typos. It should be:
VBA Code:
Sub MyCode()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last cell in column H with data
    lr = Cells(Rows.Count, "H").End(xlUp).Row
   
'   Loop through all row in column H, starting with row 2
    For r = 2 To lr
'       Multiply column I by -1 if column H is "Buy"
        If Cells(r, "H") = "Buy" Then Cells(r, "I") = Cells(r, "I") * -1
    Next r
       
    Application.ScreenUpdating = True
       
End Sub
This worked great thanks. I realised too that I didn't have the column formatted to general or number before running so I'm sure I can stick a line in that will do this. Can I ask why the End If is not required after your Next Statement?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
I realised too that I didn't have the column formatted to general or number before running so I'm sure I can stick a line in that will do this
Yes, that is simple enough to do. It can be at the beginning or end of your code (doesn't matter which).
You also do not have to do each cell individually, you can format the whole column at once.
If you are not sure what that VBA code would look like, simply turn on your Macro Recorder, and format the whole column, then stop the Recorder and view the code you just created.
Can I ask why the End If is not required after your Next Statement?
If you have the "THEN" part on the same line as the "IF" part, it is considered a one-line IF...THEN stated and does not use an END IF.
You only need to use END IF if the IF...THEN statement is not all on one line.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,517
Messages
5,636,802
Members
416,941
Latest member
shazzaxyz

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
Top