• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PROBLEMS WITH VBA CODE AND OPERATION BUTTON

Hasher

New Member
Hi of all.

The file that attached in my request is write in italian language, but, matters that i ask you solved concern VBA.

The part made in excel is compounded by:

- Five button

- 2 drop down menu

- the central part that you see colored in green is for showing the result of calculation

- the last part that is the result of reaction.


What do i ask you:

There is a problem when i click on the button that begin with the word "ANIDRIDE", because the system should update in automatic those drop down menu and the following data, but, i write an error and i don't understand what is the reason of this problem.

The second problem is relative automatic calculation:

I would like that updating automatically numbers of the following cells:


B9

L9

AD9

AO9


in such a way that the following cells will have the same number:


V8= V10

X8 = X10

Z8 = Z10

AB8 = AB10


I hope that someone can help me to solve those problems. But, i wish myself to meet some genius:)
 

Attachments

  • Chemical_reaction.xlsm
    227.9 KB · Views: 7
Sorry..
I would only like to ask you of solving the second part about what i requested in the first post.
The second problem is relative automatic calculation:

I would like that updating automatically numbers of the following cells:


B9

L9

AD9

AO9


in such a way that the following cells will have the same number:


V8= V10

X8 = X10

Z8 = Z10

AB8 = AB10

Because, the problem of the first part is a wrong formula in AE9 cell.
So, If somebody would like to solve this matter. haven't to click on button that begin with the word "ANIDRIDE", but, you can try using the first three button.
I hope that somebody would be willing to help me.
Thank you.
 
Hi ,

The formula you have in AE9 is :

=IF(LEFT($C$9,1)="H",IF(AND(M4=1,F10=-1),CONCATENATE(L4,E10),IF(M4=1,CONCATENATE(L4,(F10*-1),E10),IF(F10=-1,CONCATENATE(L4,"(",E10,")",M4),IF(M4=(F10*-1),CONCATENATE(L4,E10),CONCATENATE(L4,(F10*-1),"(",E10,")",M4))))),IF(AND(MID(C11,1,8)="Anidride",MID(M11,1,6)="Ossido"),CONCATENATE(N9,IF(O9=1,"",2),E9,IF(F9=1,"",F9),G9,H9+Q9),IF(#REF!="",CONCATENATE(#REF!,#REF!,#REF!,#REF!,#REF!,#REF!),CONCATENATE(#REF!,#REF!,"(",#REF!,#REF!,#REF!,#REF!,")",#REF!))))

Obviously , we cannot correct this formula unless you explain to us what you want the formula to do.

Narayan
 
Thank you for your answer.
This is critical point, because, it could happens two kind of situation:
-The first three drop down menù function in a correct way with this formula, so it was just setting, while,
-Number four and number five button (it begin with the word "ANIDRIDE"), repeat the situacion that you are explain above, because, when you click on this button the system come-back an error. How should it function?
I do you some examples:
CO2 + Na(OH) --> Na2CO3 + H2O
In this situation the formula in AE9 cell should be give:

- in B9 = some numbers (1 or 2 or 3....)
- in D9 = "C"
- in E9 = (the "C" number, if you doesn't see it, it values = 1)
- in F9 = "O"
- in G9 = 2

- in L9 = some numbers (1 or 2 or 3....)
- in N9 = "Na"
- in O9 = (the "Na" number, if you doesn't see it, it values = 1)
- in P9 ="O"
- in Q9 = (the "O" number, if you doesn't see it, it values = 1)
- in R9 = "H"
- in S9 = (the "O" number, if you doesn't see it, it values = 1)
- in T9 = (the number that is out of parenthesis, if you doesn't see it, it values = 1) --> This number multiply the numbers that are into parenthesis.

This is the result that i should have in the first part of sheet "Sali", and, i should use this data for forming the string that you read in AE9 cell.
In this case, If i have 1 either in B9 cell and in L9 cell. It made subtracting H, 2 and one unit of "O" number.
You can see the first problem in second part of reaction the number of "Na" is 2, while, in the first part was 1. So i don't understand how i can do the formula because i have to insert 2 in L9 cells and i have also in the first part Na = 2, but, i have also O = 2 and H = 2)
The result of reaction is:
CO2 + 2Na(OH) --> Na2CO3 + H2O
but, it isn't always so.
Because, i can see other situation, so you can do an idea:
CO2 + Ca(OH)2 --> CaCO3 + H2O --> in this case i only need to add a number in cells B9 or L9, and, the formula is automatically done.
This problem consist of button number 4 ("ANIDRIDI"+"IDROSSIDI")
 
Last edited:
In the button number 5 ("ANIDRIDI"+"OSSIDI")
I have just added the single numbers of each element for doing the formula.
For example:
CO2 + CaO --> CaO3

and i have nothing of H2O molecule.
So i hope that you understand what i mean and you can help me to solve these problems.
If you want to do me some questions...don't worry...i am available
 
Last edited:
Ok. I have fixed the last file that i attached some days ago.
I post this file correct.
So it miss of the automatically calculation of which i need
I show you again the part of previous post.
I would like that updating automatically numbers of the following cells:


B9

L9

AD9

AO9


in such a way that the following cells will have the same number:


V8= V10

X8 = X10

Z8 = Z10

AB8 = AB10

Regards.
 

Attachments

  • Reazioni e calcoli_03_rev01 - Copia.xlsm
    207.1 KB · Views: 2
Sounds like you want a multiple constraint solution. Solver might help. Have you tried some manual Solver runs?

Solver is in the Data tab on the ribbon if the Analysis Toolpak Add-In is installed. If not, add it by File > Options > Add-Ins, and add it if needed.
 
Hi Kenneth.
Thank you for your answer.
Yes you have understood what is the problem and how we can solve it.
Unfortunely, i saw that there was a wrong in the file atteched. I try to fix it and I post again.
Thank you again for your willingness
 
Hi Kenneth.
As I have promised, i have fixed my errors into the file.
I checked if Analysis Toolpak was installed...and it was Ok..you can find it in DATI-->ANALISI DATI.
Now, i attached my file again, and i hope that you can help me to solve this Rubik's problem
C:\Users\Guido\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png
:)
Thank you again.
 

Attachments

  • Reazioni e calcoli_03_rev03.xlsm
    217.7 KB · Views: 1
I tried to use excel's Solver like you suggested me.
But, it doesn't give me a result with success.
I don't know, why?
 

Attachments

  • Reazioni e calcoli_03_rev03.xlsm
    218.8 KB · Views: 0
I send you again my file.
I applied some correction to my previous file.
Maybe, somebody could have a new idea
Regards.
 

Attachments

  • Chimica_calcoli_automatici_rev.xlsm
    262.5 KB · Views: 0
I have updated my file and now I use solver like Kenneth reccomended me.
It functions enought well, but, i have still some problems that i can't solve.
- When you will click on the button anidride + ossido, cells from AO to AT are empty, i created and saved another solver for overcome this problem it exclude cells from AO to other right cells (You find this solver in follow cells:
- The first solver is saved from cell B7 to BG18
- The second solver is saved from cell BI7 to BK16)
Now, i have these problems:
1 - How can i automatically action these solver?
2 - How can i automatically action the second solver when this is writed in follow cells: C19 "anidride", M19 "ossido"
3 - How can i automatically action the solver when i will update data in cells C9 and M9.
I attached new file.
Thank you in advance.
Best regards.
 

Attachments

  • Chimica_calcoli_automatici_rev01.xlsm
    265.1 KB · Views: 1
Back
Top