If and Then Formula/Code to multiply with a Designated Cell

[aaron.mendes]

Hi Everyone,

I need a VBA code for the data in the attached sheet.

The logic goes as this.

If the filter is Full_Dispute or Partial_Dispute (Column T) and the Currency is USD (Column W), then mutiply Absolute Amount (Column U) with E2 (1)

If the filter is Full_Dispute or Partial_Dispute (Column T) and the Currency is EUR (Column W), then Divide Divide Amount (Column U) with E3 (0.79)

The result should be updated in Column C

Appreciate your assistance.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[aaron.mendes]

Hi, I copied your code into VBA and run the macro. but i dont see any change in my data.

Can you also help me understand your code since im a novice in VBA codes.

[JOHN H. DAVIS]

I first copied the information you had in Columns A, B, and D in your sample data to the Columns you referenced in Post 1. The code will look at each cell in Column T if cell = "FULL_DISPUTE" or "PARTIAL_DISPUTE" it should perform the calculations based on the Values in E2, E3 and USD, EUR. You may have been waiting for it too operate on the sample data from Columns A, B, and D. Be advised that I did change your data too confirm too the requirements you set forth in Post 1.

[aaron.mendes]

Hi John - That worked correct when i move the data to column T. Just wanted to understand the code.
What does this mean.

Please Login or Register  to view this content.

[aaron.mendes]

I have understood the code more or less. Wanted to understand how can i change the result of the macro from column C to anywhere else.

[JOHN H. DAVIS]

Hi John - That worked correct when i move the data to column T. Just wanted to understand the code.
What does this mean.

Please Login or Register  to view this content.

That statement means that if the cell in the next column to the right is not null then select the value of the cell thats three Columns to the right "USD", "EUR".

The reason I used that check was because when I was using ABS value it would error out if the cell in Column U was null.

[JOHN H. DAVIS]

This line tells it too place the results in Column C

cell.Offset(, -17)

If offsets backwards from Column T. For instance if you wanted the results to go into Column A then

cell.Offset(, -19)

Column B

cell.Offset(, -18)

[aaron.mendes]

Is it possible for a msg box to pop up instead of placing my content on E2 or E3?
the result would be that my final data would multiply with the number I input in the msg box

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Is it possible for a msg box to pop up instead of placing my content on E2 or E3?
the result would be that my final data would multiply with the number I input in the msg box

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

[aaron.mendes]

Thats great.
since i need to different values for EUR/USD, i tweaked it a little. let me know if this is correct

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Actually I realized that after Post 10. Good catch. Perfect.

[aaron.mendes]

Thanks. this helped me.

can i convert an amount in a particular column into Absolute value rather than pasting the converted absolute value in another column?
what code would do that job?

[JOHN H. DAVIS]

Same as used in the code, write it as you would in a formula.

Abs(cell.Offset(, 1))

[aaron.mendes]

If the result of my above Code is in column B (Abs value) and I have an amount in column A which is not an absolute value, what code be be required such that it will first convert column A into an ABS value and then find the difference with column A.