Vba calculation help

[hudaz]

Hi everyone,

I'm currently creating a calculator where selecting from several drop down menu's calculates a figure (basically a nz function) which in turn , depending on the calculation allocates the project as a low risk, medium risk or high risk. If the user selects certain features from the drop down menu's (such as type of machine) i want the calculation to take into account certain rules such as if a sg machine then automatically make it at least a medium risk job but i need to make it so that should the remaining calculations be high enough that it could also be a high risk job but also dynamic enough that should i remove the machine type it works as expected.

Does anyone have any experience with this ?, i've had a play around with loops etc but i can't seem to crack it :-(.

Any advice would be greatly appriciated.


Cheers!

 

[CJ_London]

In answer to your question - Yes, I have experience of this

the basic concept for calculating this might be easy or difficult, but providing the data is there and can be interpreted it can be done.

If you want a more detailed response then you need to provide more information - examples of the data and the rules to be applied and the required outcome - some examples written in English will also help

 

[hudaz]

Hi CJ_London,

Thanks for taking the time to reply I'll try to be more detailed.

I have a small number of text boxes and drop downs which require the user to input specific information relating to a container risk rating. depending on the input information i have a code running in the background that updates the risk score depending on user input. (I've attached a little snapshot showing the user input on the left and the risk scores in the right column.)

The risk scores are added together in a hidden NZ formula and if the score falls within given perimeters: <=13 Low Risk; >13 <=16 Medium Risk; >16 High Risk.

Should the user select certain features such as Colour = Feeder Colour then i need the overall calculation to be at least medium risk without affecting the risk ratings on the right hand side. I have tried code:

If Me.Text87.Value <> "Feeder Colour" Then Me.Text118.Value = 0

If Me.Text87.Value = "Feeder Colour" And Me.Text50.Value <= 13 Then

Do

Me.Text118.Value = Me.Text118.Value + 1

Loop Until Me.Text50.Value >= 13

End If

But it is not dynamic enough to change the hidden text box value as the rest of the risk scores are calculated so it in turn gives an increased risk rating because it does not update and reduce as other risk scores are calculated.

I hope this explains it better.

Thanks for your time.

Cheers!

Andy

 

[CJ_London]

help me to help you

your controls are not named properly so I have not idea which control name refers to which control

you have not described what you are trying to achieve

All I can see is your loop is endless because text50 doesn't increment so will never be >=13

it looks to me like you could just use

 If Text87<> "Feeder Colour" Then 
     Text118= 0
 else
    If Text50 <= 13 Then Text118= Text118+13-Text50
End If

another tip - you do not need .value - that is the default for the control - and Me is assumed so not required either