Absolute value

[preciouslife73]

Hi is there anyway I could generate a macro that says if column A contains cats and dogs then column c is a a dollar value change it into negative value? For example A2 = dog and C= $10.00. So if dog exist in column A then the value in column C change to -$10.00. Make sense?

[DGagnon]

you can do that with a formula if you wanted to use an extra column like this

=IF(A2="Dog",-C2,C2)

but with a macro you can do it with the worksheet_change event like this

Please Login or Register  to view this content.

you could add a second check to see if Column C contains a number as well.

[preciouslife73]

Great thanks that was fast. I prefer the macro. If I want to use more than one word like Cat, I will just enter "and "cat" " after the "dog" correct? I think this will work. Will try it first thing in the morning. Thanks!

[DGagnon]

are you looking for the word "Cat and Dog" or are you looking for "Cat" or "Dog"?

[preciouslife73]

Both cat and dog.

[preciouslife73]

I see what you mean. You are right. I think I would go with or because if only one existed then it change the value. You got me thinking.

[DGagnon]

so the sell must contain both words?

would they be the only words in the cell? you mean need to do a string search.

[DGagnon]

to find either value you could use this:

Please Login or Register  to view this content.

[preciouslife73]

Thank you! I'm trying to give you a star not sure if I did it right. Again thank u so much.

[DGagnon]

You did, i see it, thank you. I am glad i was able to help.

[preciouslife73]

Hi I tried to test it this morning but cant see the macro on the Macro dialog box to run it. how do I run a Private Sub?

[DGagnon]

you need to enter it into the worksheet module, it shoudl automaticaly run from there.

[preciouslife73]

I have it in the worksheet module as a master Micro enable workbook. so if I want to run this function on another workbooks I just open the master Micro enable workbook and run it from there.

I see what you mean, i also copied to the original data file and it seems to attempt to run automatically but there is an issue with the Type mismatch on the (Target, "A;A")

[DGagnon]

ya this macro is designed to run on the sheet, it has to be loaded into each sheet that you want to use it on.

[preciouslife73]

there is a mismatch on the (Target, "A:A")

[DGagnon]

Test this attachment out and see if you can replicate this into your book

[preciouslife73]

It works great. the only issue is if I paste the data on top of that sheet which has 300 rows worth of data it gives me the overflow warning. then i had to double click on each row of Column A to update the column C individually.

[preciouslife73]

"If Target.Columns.Count > 1 Then Exit Sub" this solved the overflow issue. but still need to double click on column A to get the C adjusted.

[DGagnon]

it is designed to run when the cell changes, you could change it so that it will run when the cell is selected if you want. the question is when do you want the macro to trigger?

[DGagnon]

note: i did make a change to the code about 4 min after posting the initial solution, this is the most updated version i posted

Please Login or Register  to view this content.

[preciouslife73]

I would like the Macro to trigger when I paste the data to that sheet then change the entire column.

[DGagnon]

Try this macro, slight modification to acomidate a range

Please Login or Register  to view this content.

[preciouslife73]

PERFECT! Thank you!

[DGagnon]

no problem, Glad i could help.