HELP!!!!! Macro does not work

[Brutium]

Hello Anybody willing to lend a hand?

I have a button that uses the following macro:


'Ensure weights sum to 100% and are placed correctly
msg = ""
toolsCt = WorksheetFunction.CountA(toolsRng)
weightsCt = WorksheetFunction.Count(weightsRng)
Select Case toolsCt - weightsCt
Case 0
If Range("SumOfWeights").Value <> 1 Then
msg = msg & "Weights must sum to 100% - please correct and try again." & vbCrLf & vbCrLf
End If
Case Is > 0
If Range("SumOfWeights").Value <> 1 Then
msg = msg & "Weights must sum to 100% - please correct and try again." & vbCrLf & vbCrLf
End If
msg = msg & "Each Eval Tool must be given a weight - please correct and try again."
Case Is < 0
If Range("SumOfWeights").Value <> 1 Then
msg = msg & "Weights must sum to 100% - please correct and try again." & vbCrLf & vbCrLf
End If
msg = msg & "There must be an Eval Tool for each weight - please correct and try again."
End Select
If msg <> "" Then
Application.Calculation = xlCalculationAutomatic
MsgBox msg
Exit Sub
End If

The above macro is supposed to look at column C and get the information from C8:C17 if the sum adds up to 100%, then the macro will create a number of additional sheets.

My problem is that even though the addition in C18 is 100%, the macro keeps telling me that the "Weight must sum to 100%"
I cannot resolve the problem, and I would like to share my program with my colleagues.

Any help?

 

[Joe4]

You can post back whenever you like, and I will respond when I am around (I am away from the computer a bit more on weekends).
If you think it may be easier, you can post a copy of your file to a file sharing site and post a link here, if you would like me to look at your file directly. Just be sure to first "scrub" it of any sensitive data.

 

[Brutium]

Hello Joe4,
This link has my spreadsheet.
https://dl.dropboxusercontent.com/u/66153530/COPY_MarksCalculatorV4.xlsm

If you put anything in the column called Weight as % of Final, even though it adds up to 100% the program tells me that it has to add up to 100%. Can you help?

Thank you.

You can post back whenever you like, and I will respond when I am around (I am away from the computer a bit more on weekends).
If you think it may be easier, you can post a copy of your file to a file sharing site and post a link here, if you would like me to look at your file directly. Just be sure to first "scrub" it of any sensitive data.

 

[Joe4]

I'll take a look tonight when I am at home and can download your file.

 

[Brutium]

I'll take a look tonight when I am at home and can download your file.

Thank you. My colleagues and I will be VERY happy and appreciative!

 

[Joe4]

I think you may have a floating arithmetic situation going on here: Floating-point arithmetic may give inaccurate results in Excel

You can get around by rounding each of your calculations, i.e. changing:

Case 0
    If Range("SumOfWeights").Value <> 1 Then

to

Case 0
   If Round(Range("SumOfWeights").Value, 2) <> 1 Then

Repeat for your other two Case statements.

 

[Brutium]

Hello Joe4,

That seems to have been the error-correction I needed.

THANK YOU very much. The file now seems to be working fine!!!!

Brutium

P.S. It is always nice to know that there are people out there willing to lend a hand.....

I think you may have a floating arithmetic situation going on here: Floating-point arithmetic may give inaccurate results in Excel

You can get around by rounding each of your calculations, i.e. changing:

Case 0
    If Range("SumOfWeights").Value <> 1 Then

to

Case 0
   If Round(Range("SumOfWeights").Value, 2) <> 1 Then

Repeat for your other two Case statements.

 

[Joe4]

Glad we got it worked out!

Initially, I tried 50%, 30%, and 20%, and that worked fine with the original code.
The I tried what you tried, 40%, 30%, 20%, and 10% and got the same error you did.
The way Excel stores numbers, sometimes there are ever so minor differences (as discussed in the link I provided). Rounding gets rid of that.
Just one of those Excel quirks... Luckily, there aren't too many.

 

[Brutium]

Once again THANK YOU!!!!

I showed the file to my colleagues and they love it.
Thanks for finding out what the problem was!

Brutium

Glad we got it worked out!

Initially, I tried 50%, 30%, and 20%, and that worked fine with the original code.
The I tried what you tried, 40%, 30%, 20%, and 10% and got the same error you did.
The way Excel stores numbers, sometimes there are ever so minor differences (as discussed in the link I provided). Rounding gets rid of that.
Just one of those Excel quirks... Luckily, there aren't too many.