Help to streamline my code.

[gpwaters]

I'm new to the forum and scripting/Macro Excel. I have a calculation sheet that the user inputs weight by pounds into a cell (G5), then in the cells following (H5, I5, J5, L5, M5, N5), the user inputs ounces. With the following code this allows me to have the user input ounces in one cell (H5) and then a percentage is calculated and outputted into the same cell (H5). Basically the formula is simple, oz. x 0.0625 / lbs. * 10. I found this code on the web, modified it and it worked great. Now I've added a little more to the formula and I get "Procedure too large". Not surprising I guess. HELP!!

Here's my code:

Please Login or Register  to view this content.

Every time I try to simplify I get errors. I would appreciate help, a litle frustrated at the moment.

Thank you

[watersev]

try this in a woksheet module:

Please Login or Register  to view this content.

[MarvinP]

Hi gpwaters and welcome to the forum,
Here would be the first Improvement on your code, if I was your teacher.

Please Login or Register  to view this content.

[gpwaters]

try this in a woksheet module:

Please Login or Register  to view this content.

Wow, thanks for the amazingly fast reply. And it's working perfectly. I inserted your code into my "If Range ("XX").Value <> "" to keep a run-time error from occuring when a value is deleted in one of the cells and its working great.

Thanks again.

[MarvinP]

Looking at all the Case statement repitition I'd suggest something like this after you thought about it a while.

Please Login or Register  to view this content.

Hope all this helps - there are a lot of ways to write code that doesn't work, a few less that work and fewer yet that you can read and understand a few months from now.

[nimrod]

A couple of error checks to do is that the source cell is actually a number and that the value in G5 or G6 is also a number.
Also good idea to trap for errors and EnableEvents even if there is an error/crash
This solution does an update for a change in any cell in the range ("I5:U6"),
To increase the range of cells, i.e. row or columns, you only need to change one thing '("I5:U6"), .. and your done

Please Login or Register  to view this content.

[nimrod]

This sol'n will put error statement in source if you enter invalid information ie. zeros , blanks , text ... etc
This solution does an update for a change in any cell in the range ("I5:U6"),
To increase the range of cells, i.e. row or columns, you only need to change one thing '("I5:U6"), .. and your done

Please Login or Register  to view this content.

[snb]

or

Please Login or Register  to view this content.

[gpwaters]

Thank you nimrod.

Althought watersev's answer worked very well, I still had a few issues. Your answer has been exactly what I've been looking for. I knew with such repetition in my code there had to be a way to narrow it to a very small block, which yours did perfectly.

Thanks again

A couple of error checks to do is that the source cell is actually a number and that the value in G5 or G6 is also a number.
Also good idea to trap for errors and EnableEvents even if there is an error/crash
This solution does an update for a change in any cell in the range ("I5:U6"),
To increase the range of cells, i.e. row or columns, you only need to change one thing '("I5:U6"), .. and your done

Please Login or Register  to view this content.

[nimrod]

HI gpwaters:
Glad you liked the sol'n , thx for the feedback.

Cheers