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]

I am guessing that maybe you have some rounding issues in your Sum (so maybe your totals work out to be something like 99.9999% or 100.000001%.
See if applying some rounding to your functions resolves that issue.

 

[Brutium]

Joe4, Thank you for your prompt reply.

I do not think this is a rounding issue since I am using in C8:C17 numbers such as 40, 30, 20, 10. The spreadsheet is designed to change these values to % so that in C18 I do have 100%.

Any other idea? I would very much appreciate it....

 

[Joe4]

I do not think this is a rounding issue since I am using in C8:C17 numbers such as 40, 30, 20, 10. The spreadsheet is designed to change these values to % so that in C18 I do have 100%.

Exactly how is it changing these value to percent? What are your formulas? Please provide details!

As you probably know already, if you add up 40,30,20,10, you will get 100, not 100% (100% is actually equal to 1).
But also note if you are using functions like "TEXT", it will return a Text value, not a Numeric one, so mathematical comparisons will probably not work on that.
We would have to see how you are doing your conversion to see what the problem may be.

 

[Joe4]

In addition to my previous post...

I see that you have:
Application.Calculation = xlCalculationAutomatic
near the end of your code.

What is that there? Are you on manual calculation mode before that? If so, why?
Depending on the timing of when this data is being entered and when you are calculating, your might need to switch to Automatic calculation mode, or force a calculation before you run your code.

 

[Brutium]

The user can change a number of entries in a list so that eventually the user can add or delete sheets.

Another part of the macro states the following:

'Summarize changes made
Select Case addedShtCt
Case 0
msg = "No new sheets were added to your workbook." & vbCrLf & vbCrLf
Case Is > 0
msg = addedShtCt & " Sheets were added to your workbook." & vbCrLf & vbCrLf
End Select
Select Case deletedShtCt
Case 0
msg = msg & "No existing sheets were deleted from your workbook."
Case Is > 0
msg = msg & deletedShtCt & " Sheets were deleted from your workbook."
End Select
toolsSht.Activate
masterSht.Visible = xlSheetVeryHidden
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
End With
MsgBox msg
End Sub


I guess the "Application.Calculation=xlCalculationAutomatic" is needed in order take into account the changes...

 

[Joe4]

I guess the "Application.Calculation=xlCalculationAutomatic" is needed in order take into account the changes...

If you worksheet is already set to Automatic calculation, then this line is not necessary.
If it is set to Manual calculation, you might need to force a calculation before you run the code to check the sum of the percentages.

BTW, did you miss my post above (#4)? You didn't answer the questions I posted there.

 

[Brutium]

Sorry Joe4, yes I did miss post #4.

I understand that, my cells C8:C17 are formatted to accept the values put in them as %, so that C17 will add up to 100% = 1 and that is what the various Cases in post #1 refer to.

Regarding Automatic or Manual calculations, I am not sure. Maybe I should comment the line that talks about Automatic Calculation and see how the program responds...

 

[Joe4]

I understand that, my cells C8:C17 are formatted to accept the values put in them as %, so that C17 will add up to 100% = 1 and that is what the various Cases in post #1 refer to.

Try this. Try an example where you are saying it is not working.
Then go out to your spreadsheet and in any blank cell, enter this formula and tell me what it returns:
=C17=1

Regarding Automatic or Manual calculations, I am not sure. Maybe I should comment the line that talks about Automatic Calculation and see how the program responds...

You are not understanding what I am telling you. Having it at the end of your code won't help you.
If your worksheet is in Manual Calculation mode (should be easy to tell, see: Controlling Excel Calculation - Decision Models), you need to have that code at the beginning of your code. Or else your calculations won't be apply until AFTER you have already run your VBA code. They need to be calculated BEFORE your VBA code is applied.
So, you either need:
1. Your spreadsheet to be in Automatic calculation mode from the start (and make sure no section of VBA code is changing it to Manual)
- or -
2. You need to calculate (or switch to Automatic calculation mode) BEFORE you run the code that checks to percentage total

If your spreadsheet is already in Automatic calculation mode, as long as there isn't any place in your code that is switching to Manual calculation mode, there is no need for that line at all.

 

[Brutium]

Hello Joe4, I am sorry but I cannot stay at the computer any longer today. Would it be possible to contact you later on tonight or maybe tomorrow?

Try this. Try an example where you are saying it is not working.
Then go out to your spreadsheet and in any blank cell, enter this formula and tell me what it returns:
=C17=1


You are not understanding what I am telling you. Having it at the end of your code won't help you.
If your worksheet is in Manual Calculation mode (should be easy to tell, see: Controlling Excel Calculation - Decision Models), you need to have that code at the beginning of your code. Or else your calculations won't be apply until AFTER you have already run your VBA code. They need to be calculated BEFORE your VBA code is applied.
So, you either need:
1. Your spreadsheet to be in Automatic calculation mode from the start (and make sure no section of VBA code is changing it to Manual)
- or -
2. You need to calculate (or switch to Automatic calculation mode) BEFORE you run the code that checks to percentage total

If your spreadsheet is already in Automatic calculation mode, as long as there isn't any place in your code that is switching to Manual calculation mode, there is no need for that line at all.