Changing Existing Values with a Macro/VB Code

[DrMoorehead1983]

I am relatively new to programming in VB in Excel so maybe this is very simple to do. I have a list of amounts that are being copied into a an excel sheet and I need to be able to change whether they are positive or negative based on the next column to the right having either Debit or Credit as their value. Currently they are all absolute values of the ones I need them to be. Technically, all I really need to test for is the Credits and the rest would remain the same.

Does anyone have any input on this?

[jasoncw]

Assuming the amounts are in column A, Dr/Cr is in column B, and the amounts start in row 10, you could use something like this procedure:

Please Login or Register  to view this content.

Change the red font above if the rows or columns are different on your workbook.

HTH

Jason

[DrMoorehead1983]

It wants to change all the values associated with "debit" back to positive numbers whenever that script runs again.

I added the code to a button to ease user interface, but every time the button is clicked with that code it changes every instance by multiplying -1. I tried adding to the if statement as follows

Please Login or Register  to view this content.

but it continues to do it. Is that due to the way those values are stored in Excel?

It does work well other than that though.

[DrMoorehead1983]

Sorry, like I said new. I should have a value for the cell referenced by lastRow. Because lastRow is always going to be +

Like I said, a bit of a beginner.

[jasoncw]

Sorry, I assumed all amounts were positive, and you wanted to change the debits to negatives. I didn't realize it was the other way around for you. It looks like you figured it out, though, correct?

The one line I would change is:

Please Login or Register  to view this content.

You don't need the LCase function there. This converts a string to all lower case letters. So just use:

Please Login or Register  to view this content.

Jason

[DrMoorehead1983]

Well, that makes sense. LCase = Lower Case.

No, the problem was that every time the code ran, it would take the values and multiply them by -1 which means that the first time it ran it would work fine, but the next time it ran it would convert them back to positive numbers (multiply by -1)

I do appreciate the help though, I spent a couple of hours trying to figure out a way to get the formatting correct.

Thanks a lot!

D

[jasoncw]

Good point. Yes, I see the problem now. Glad you were able to resolve it.

Jason