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

msohare91

New Member
Joined
Jun 8, 2020
Messages
15
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.
 

msohare91

New Member
Joined
Jun 8, 2020
Messages
15
Office Version
  1. 2016
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.
Yes I put it at the start thank you.

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.
Ah I did not know this! Great to know thank you very much for your help!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,683
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,758
Members
416,202
Latest member
donya ba

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