Excel and Visual Basic

[jbento]

All,
I have a cell where I enter currency ($544.00). I would like to OnExit of the cell, have it calculate automatically $544.00 divided by 4, and then the answer should replace $544.00 in the same cell.

Can I do visual basic or something else to get the results?

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[TonyJollans]

Hi jbento,

You might be able to do this with code in the WorkSheet_Change event but it isn't straightforward and it strikes me as a mistake waiting to happen.

I would recommend you have another cell which held the computed result, so if you entered 544.00 in cell A1 and had the formula =A1/4 in cell B1 it would display 136.00.

Enjoy,
Tony

 

[PepeLeMokko]

To follow Tony's answer up:
if you want A1 to display the contents of B1 :
record a new macro ( tools - macro- record new macro)and do the following :
right click on B1 KEEPING THE BUTTON DEPRESSED, move the pointer to the upper part of the selected cell and drag to A1. You will get a nice menu after you let go the mouse button and choose " copy here as value only".
Then select B1 and delete ( if you want to) the contents.
Assigning this little macro to a taskbar button will give you what you want without having to program VBA.
You can of course insert this procedure in a module to shut down the workbook one this is finished.
The first part is :
Range("B1&quot.Select
Range("B1&quot.Copy
Range("A1&quot.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B1&quot.Select
Selection.ClearContents

 

[jbento]

I just want to say thanks for all the help I got on this one.

I figured it out, and I think it is a good workaround.

I created two worksheets called: "DataEntry" & "Invoice"

I did the calculations on the DataEntry worksheet and did links of the answers on the Invoice worksheet, and that seemed to work just fine.

Again, thanks so much for all the help I got.

Have a great day!!!

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[SkipVought]

Hi,

I agree with what Tony has suggested. Here's the, mistake waiting to happen.

You enter 544 and the program "automatically" CHANGES the value to 136.

Well, guess what! THAT'S a change too! So the program's gonna want to continue to divide by 4.

Actually, that problem can be solved, but what later someone does an F2 (Edit) on that cell. Program divieds by 4!

See how messy?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com