If-statement with relative values

[joshua1990]

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]

If Worksheets("tblData").Range("E30").Value <> 1 Or Worksheets("tblData").Range("G50").Value <> 1 Then

[joshua1990]

Thanks, it works perfect!

[joshua1990]

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]

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]

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]

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]

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]

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]

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.


Example1.xlsm

[mdmackillop]

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]

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/...sults-in-excel