VBA Code

[vioravis]

I am looking for VBA code by which the results can be obtained without having to run the code. For Instance, if Z = X*Y, I would like the code to automatically calculate Z for as soon as the value of X and Y are changed. I also do not want to use forms. Can someone please help me? Thanks.

[royUK]

Are X and Y values in cells and are these cells fixed?

[vioravis]

Yes. X and Y are values in Cells and these cells are fixed. Thanks.

[jasoncw]

Assuming x and y are in cells A1 and A2, and you want z to be posted in cell A3, you can right-click the sheet tab, select view code, and copy this code:

Please Login or Register  to view this content.

HTH

Jason

[vioravis]

Thanks, Jason. This works fine. Can you please explain this code and what it does?

Please Login or Register  to view this content.

[boylejob]

vioravis,

Please Login or Register  to view this content.

This code is lookings to see if two ranges intersect. The first range, Target, is the cell that has been changed on the worksheet. The second range has been defined as the cells that contain your X and Y values. Therefore, if the Target cell is either X or Y, you have an interection and your code fires.

The If statement is basicly a double negative (Not Nothing) which gives you a positive. If something is Not Nothing then it is Something, is another way of explaining it. If the intersection is nothing then you don't have an intersection so the Else part of the If statement is performed. If the intersection is Not Nothing then you have an intersection and the Then part of your If statement is performed.

Jason, please correct me if my explanation is incorrect. I am answering this question to make sure my thinking is correct.

Hope this helps.

[vioravis]

Thanks, boylejob.

Will it make a difference if I don't have this line there. I removed it and seems to work fine.Also, what should I do if X and Y are in different worksheets?

[mikerickson]

... if Z = X*Y, I would like the code to automatically calculate Z for as soon as the value of X and Y are changed.... X and Y are values in Cells and these cells are fixed. Thanks.

If you prefer a UDF.

Please Login or Register  to view this content.

can be called from a worksheet cell. If your values are in A1 and A2, the spreadsheet formula
= myProduct($A$1,$A$2)
can be put anywhere

[Leith Ross]

Hello vioravis,

This relates to Mike's code. The follow code is Mike's except an additional line has been added. This line ensures that the macro auto updates whenever a cell value that it uses is changed. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[vioravis]

Thanks, mike and leith. I am not sure if I can use the function mike has given since my calculations are quite complicated with lots of variables. I was just trying to present an easier version of my problem with X's and Y's.

I am trying to use the code given by Jason to copy a text from one sheet to another sheet. The problem with this is I need to double click somewhere in the worksheet to change the results? Can someone please tell me how to avoid this? Thank you.

Please Login or Register  to view this content.

[mikerickson]

Put that code in the code module for both sheets.

The function I posted was also an example. It should be possible to create a UDF that will return the value you want, perhaps with more arguments.