PDA

View Full Version : If-statement with relative values



joshua1990
07-28-2017, 07:38 AM
Hey guys!


I just can not go any further. However, a button MsgBox is opened, if at least 1 of 2 cells does not contain "100%" as values. The If statement looks like this:


If Worksheets("tblData").Range("E30").Value Or Worksheets("tblData").Range("G50").Value <> 100 Then Antwort = MsgBox...



Here it refers only to absolute values, but relative (%) values ​​are contained in the cells themselves.




How is this to be solved?


Best regards

Kenneth Hobs
07-28-2017, 09:16 AM
If Worksheets("tblData").Range("E30").Value <> 1 Or Worksheets("tblData").Range("G50").Value <> 1 Then

joshua1990
07-29-2017, 02:21 AM
Thanks, it works perfect!

joshua1990
07-29-2017, 03:01 AM
I'm sorry for the double post.


How does it look if I do not refer to the relative values ​​of a cell, but to the sum of two ranges?


With the following approach, unfortunately, always get an MsgBox



Sub CheckSum()
Dim Answer As Integer
Dim rOne As Range
Dim rTwo As Range
Dim sOne As Variant
Dim sTwo As Variant

Set rOne = Sheets("tblOne").Range("F1:F9")
Set rTwo = Sheets("tblOne").Range("E15:E51")
sOne = Application.WorksheetFunction.Sum(rOne )
sTwo= Application.WorksheetFunction.Sum(rTwo )

If sOne <> 1 Or sTwo <> 1 Then
Answer = MsgBox("***", vbCritical + vbYesNo, "***")
If Antwort = vbYes Then
Exit Sub

ElseIf Antwort = vbNo Then
Worksheets("tblTwo").Select

End If

ElseIf sOne= 1 And sTwo = 1 Then
Worksheets("twlTwo").Select End If
End Sub

Kenneth Hobs
07-29-2017, 06:04 AM
When pasting code, please use code tags. Click the # icon to insert them.

I corrected some typos.

Sub CheckSum()
Dim Answer As Integer
Dim rOne As Range, rTwo As Range
Dim sOne As Double, sTwo As Variant

Set rOne = Sheets("tblOne").Range("F1:F9")
Set rTwo = Sheets("tblOne").Range("E15:E51")
sOne = Application.WorksheetFunction.Sum(rOne)
sTwo = Application.WorksheetFunction.Sum(rTwo)

If sOne <> 1 Or sTwo <> 1 Then
Answer = MsgBox("***", vbCritical + vbYesNo, "***")
If Answer = vbYes Then
Exit Sub
ElseIf Answer = vbNo Then
Worksheets("tblTwo").Select
End If
ElseIf sOne = 1 And sTwo = 1 Then
Worksheets("twlTwo").Select
End If
End Sub

mdmackillop
07-29-2017, 06:14 AM
If any values in these ranges are the result of calculation then probably rounding errors mean the totals are not exactly 1. Try something like

If Abs(sOne - 1) > 0.001 Or Abs(sTwo - 1) > 0.001 Then
Antwort = MsgBox("***", vbCritical + vbYesNo, "***")
If Antwort = vbYes Then
Exit Sub
ElseIf Antwort = vbNo Then
Worksheets("tblTwo").Select
End If
ElseIf Abs(sOne - 1) < 0.001 And Abs(sTwo - 1) < 0.001 Then
Worksheets("twlTwo").Select
End If

Note your typo here "Answer = MsgBox("***","

joshua1990
07-29-2017, 06:24 AM
Hey Kenneth Hobs!

Thanks for the support and the hint.



Unfortunately the fileupload of my tablet just does not work, otherwise I would upload the file.


I now have the problem that empty cells or text are also contained in the two areas, which are added up, if no value is stored there.
So 5%; 10%; /; 10%, etc.


How should I deal with this?
Unfortunately, with your extension, you will receive an error message for the sum function.

mdmackillop
07-29-2017, 07:04 AM
If your cells are constants then you could use SpecialCells
Set rOne = Sheets("tblOne").Range("F1:F9").SpecialCells(2, 1)
or if Formulae
Set rOne = Sheets("tblOne").Range("F1:F9").SpecialCells(-4123, 1)
If these are not suitable, you could loop and check for numerical values.

Kenneth Hobs
07-29-2017, 10:54 AM
You might want to use Debug.Print or MsgBox to show the Sum value to see if rounding issues might occur as mdmackillop said.

Of course if you have an error in a formula in a range, Sum will fail.

Here is a quick way to use Immediate Window to see a result after pressing Enter key.

?worksheetfunction.Sum([A1:A3].SpecialCells(xlCellTypeFormulas))

joshua1990
07-30-2017, 04:21 AM
Hey guys!


Attached is a minimalized sample file with the relevant tables.
Unfortunately, I do not get it with your approaches: /


Where is the error?
With Special Cells it does not work either.


19937

mdmackillop
07-30-2017, 04:42 AM
The totals do not = 1. There is an error of 2.22044604925031E-16

Try this to demonstrate. Post#6 shows one method to get around this.

Set rOne = Sheets("tblOne").Range("E3:E18").SpecialCells(2, 1)
Set rTwo = Sheets("tblOne").Range("E24:E40").SpecialCells(2, 1)
sOne = Application.WorksheetFunction.Sum(rOne)
sTwo = Application.WorksheetFunction.Sum(rTwo)

If sOne <> 1 Or sTwo <> 1 Then
Debug.Print sOne - 1
Debug.Print sTwo - 1

Kenneth Hobs
07-30-2017, 09:29 AM
Another way to avoid the floating point error, is to round. e.g.

sOne = Round(Application.WorksheetFunction.Sum(rOne), 2)
sTwo = Round(Application.WorksheetFunction.Sum(rTwo), 2)
https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel