Calculation using Macro ( MS Excel 2007 )

**hally** · 01-06-2008, 08:59 PM

Good day..

I want to do simple calculation using Macro in MS Excel 2007..

All the data is in Sheet1 ( Which is 13a ), the calculation is in Sheet2 ( Which is work ) and the result of the calculation will come out in Sheet3 ( Which is result ).. Here is my coding..

Please Login or Register  to view this content.

Here is my attachment.. Maybe some of you can help me..

Thank you so much for your kind help..

**shg** · 01-06-2008, 09:40 PM

Please Login or Register  to view this content.

**hally** · 01-06-2008, 09:59 PM

Thank you shg..

But it still didn't work.. how about for example the user want to do the math operation beside addition.. maybe my coding is wrong somewhere..

**shg** · 01-06-2008, 11:12 PM

hally, your code is wrong; my code is a correction of your code. I don't know what you want to do.

**Paul** · 01-06-2008, 11:42 PM

Hi Hally, try this code instead:

Please Login or Register  to view this content.

After pasting this code into your module, remember to right-click on your command button, select 'Assign Macro' and choose 'myMacro' from the list of available macros. Set your values in cells C6:G6 on sheet 'work' and click the button. The sheet listed in G6 will be updated accordingly.

**hally** · 01-06-2008, 11:53 PM

Thank you both of you..

pjoaquin,

How to:

Set your values in cells C6:G6 on sheet 'work' and click the button. The sheet listed in G6 will be updated accordingly.

I still don't know and still get error..

Thank you so much for help..

**Paul** · 01-06-2008, 11:56 PM

What error are you getting? We can't help you if we don't know.

**Paul** · 01-07-2008, 12:01 AM

Here's your sheet with the code I gave you, and with values pre-populated in cells C6:G6 of the sheet named "work". Click calculate, and the cell referenced in E6 on sheet "work" will be updated on sheet "result" based on the operator and value in C6 and D6 on sheet "work".

Further modifications may need to be done to the math portion, but it should at least update and run properly.

**hally** · 01-07-2008, 12:03 AM

Dear pjoaquin,

After I put the operation box is +, the value is 10, ( this an example only )then I click the Calculate button.. Come out the Error ( Runtime Error 9.. Subscript out of range ).. I assign the macro already..

Please Login or Register  to view this content.

**Paul** · 01-07-2008, 12:12 AM

Hally, I think the issue might be that in your copy of the worksheet, you have an invalid sheet name in cell F6 (and possibly G6). The values in F6 and G6 must match EXACTLY the sheet name.

In the original file you uploaded, you put "tab13a" in F6 and "result" in G6. There is no such sheet as "tab13a", so that would throw a subscript out of range error. Change that to just "13a" (without the quotes) since that is the name of the worksheet.

**hally** · 01-07-2008, 02:25 AM

Originally Posted by pjoaquin

Hally, I think the issue might be that in your copy of the worksheet, you have an invalid sheet name in cell F6 (and possibly G6). The values in F6 and G6 must match EXACTLY the sheet name.

In the original file you uploaded, you put "tab13a" in F6 and "result" in G6. There is no such sheet as "tab13a", so that would throw a subscript out of range error. Change that to just "13a" (without the quotes) since that is the name of the worksheet.

Yes.. thank you so much.. I can solve the problem already.. thank you so much my friend..

**Paul** · 01-07-2008, 02:35 AM

You're very welcome. Glad I could help you out.

**hally** · 01-07-2008, 03:48 AM

Dear pjoaquin..

How I want to combined this 2 system..?? I mean the first system is DEMAND combined together with balance file so it will become one system..

I have done the coding for DEMAND file.. I try to combine it but still confius in how to do it..

Thank you for help me..