Help w/ Unit Conversions

[xxxombie88]

Ok, so I think I've got this workbook where I want it to be for the most part. However, I've reached a hurdle that I can't seem to get over!

I would like for the macro "Update Inventory" to subtract the value of C3 from the chosen inventory item in B2. Though since C3 is the result of converting (C2, D2, D3) the macro errors out if C3 = #N/A. This causes a problem where some of our materials are used by the dozen(dzn)! So what I'm having trouble with is making the macro work around the #N/A value and subtract the value of C2 from the chosen inventory item in B2 if there is no converted value in C3.

Any help and/or advice is very much appreciated!

[xxxombie88]

bumping this since i made changes and don't feel it necessary to make a new thread

[xLJer]

How about this formula option.

"=IF(C2>0,CONVERT(C2,D2,D3),0)"

[xxxombie88]

that formula is fine, thank you! However I'm trying to get the VBA to argue "if C3 = #N/A then subtract C2 from 'Inventory'" but I can't seem to figure out where the VBA needs to be changed to allow this.

[xLJer]

I don't have a solution to your Excel version 2007 workbook. You have code
for the worksheet change event and command button click event in two places,
in Module1 and in code behind the Inventory sheet.

My experience is with Excel 2003, so I'll leave it to the forum experts to
say whether this duplication is a problem, and what code is working when the
Update Inventory button is clicked or a sheet change event is triggered.

[xxxombie88]

thanks for the help! i know the code is in "Module" and "Sheet" mainly because i was using the module on the final sheet and have been storing/logging the changes and updates i make to the code in a folder incase i decide to use the module for something else later. just a personal thing

still haven't been able to put a code together that works around the #N/A value so until then i've had to designate "u" as "1dzn" (1 dozen) for the formula to work. i'd like to avoid this as some of our personnel may get confused, but if this is the only work around for what i'm trying to achive then meh i'm sure i can get my supervisor to allow it.

also, pertaining to what i just mentioned, how would i be able to make it so a msgbox comes up saying "units don't match!" if the value of C3 = #N/A? haven't had luck since the cell contains a formula but this would help if anyone tries to run the macro with non-like units and keeps them out of the vba debug.


thanks again for any help!

[xLJer]

Here's how to get the message out:

Please Login or Register  to view this content.

Sorry I can't be of more help. There are incompatibilities when I try
to run the code from Excel 2003.