VBA: Alert if Base or 2*Base

[Apple1]

Using Excel 2010.

I want Excel to alert me if a cell value is NOT equal Base or 2*Base, but when a call is NOT Base, it could be 2*Base, or vice versa; hence Excel always tell me it is wrong.

How should I modify my below code? Thank you

Sub SubTotal_test()



Dim k, i, j, minim, countleft, base, tmp_row, Last_Row, rw As Integer



base = InputBox("State the number of items")
Last_Row = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row



For i = Last_Row + 1 To 1 Step -1

If (ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And ActiveWorkbook.ActiveSheet.Cells(i, 11) <> base) Then
If (ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And ActiveWorkbook.ActiveSheet.Cells(i, 11) <> (2 * base)) Then

MsgBox "There is an error with the SubTotal. Please change manually."

Exit For
End If

Else

'MsgBox "SubTotal OK"
ActiveWorkbook.ActiveSheet.Cells(i, 11).Select
End If
Next i



End Sub

 

[JackDanIce]

Try:

Sub subTotal()

    Dim x       As Long
    Dim LR      As Long
    Dim base    As Long
    Dim strMsg  As String
    
    base = InputBox("State the number of items: ")
    strMsg = "Error with subtotal at row: @x@1@1Please change manually"
    
    Application.ScreenUpdating = False

    With ActiveSheet
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        
        For x = LR + 1 To 1 Step -1
            If .Cells(x, 3).Value = "L" Then
                If .Cells(x, 11).Value <> base Or .Cells(x, 11).Value <> 2 * base Then
                    strMsg = Replace(strMsg, "@x", x)
                    strMsg = Replace(strMsg, "@1", vbCrLf)
                    MsgBox strMsg, vbExclamation, "Sub-Total Error"
                    Exit For
                Else
                    MsgBox "SubTotal OK", vbOKOnly, "SubTotal OK"
                End If
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[amiroo]

change your if like this:

If ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And (ActiveWorkbook.ActiveSheet.Cells(i, 11) <> base Or ActiveWorkbook.ActiveSheet.Cells(i, 11) <> (2 * base)) Then

 

[Apple1]

change your if like this:

If ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And (ActiveWorkbook.ActiveSheet.Cells(i, 11) <> base Or ActiveWorkbook.ActiveSheet.Cells(i, 11) <> (2 * base)) Then

Hi I tried both yours and the Ans before you. Neither works. As I said, there seems to be the issue when a cell is NOT Base, it could be 2*Base, or vice versa; hence Excel always tell me it is wrong.

Thank you

 

[JackDanIce]

Try:

Sub subTotal()

    Dim x       As Long
    Dim LR      As Long
    Dim base    As Long
    Dim strMsg  As String
    
    base = InputBox("State the number of items: ", "")
    strMsg = "Error with subtotal at row: @x@1@1Please change manually"
    
    Application.ScreenUpdating = False


    With ActiveSheet
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        
        For x = LR + 1 To 1 Step -1
            If .Cells(x, 3).Value = "L" Then
                Select Case base
                    Case base, base * 2
                        MsgBox "SubTotal OK", vbOKOnly, "SubTotal OK"
                    Case Else
                        strMsg = Replace(strMsg, "@x", x)
                        strMsg = Replace(strMsg, "@1", vbCrLf)
                        MsgBox strMsg, vbExclamation, "Sub-Total Error"
                        Exit For
                End Select
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[Apple1]

Try:

Sub subTotal()

    Dim x       As Long
    Dim LR      As Long
    Dim base    As Long
    Dim strMsg  As String
    
    base = InputBox("State the number of items: ", "")
    strMsg = "Error with subtotal at row: @x@1@1Please change manually"
    
    Application.ScreenUpdating = False


    With ActiveSheet
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        
        For x = LR + 1 To 1 Step -1
            If .Cells(x, 3).Value = "L" Then
                Select Case base
                    Case base, base * 2
                        MsgBox "SubTotal OK", vbOKOnly, "SubTotal OK"
                    Case Else
                        strMsg = Replace(strMsg, "@x", x)
                        strMsg = Replace(strMsg, "@1", vbCrLf)
                        MsgBox strMsg, vbExclamation, "Sub-Total Error"
                        Exit For
                End Select
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Thank you for the reply. But it still doesn't work. I am not sure why.

 

[Peter_SSs]

Could we see a small set of sample data and have an explanation in words of what the code should be doing for that sample data?


Other comments:

Dim k, i, j, minim, countleft, base, tmp_row, Last_Row, rw As Integer

The only Integer declaration in this line is rw, all the other variables will be Variant type. If you want them all to be declared as Integer, you need to specify each one
Dim k as Integer, i as Integer, j as Integer, ...



The blue parts in all of the following will always be True (unless possibly if base = 0 for the first two codes)

                If .Cells(x, 11).Value <> base Or .Cells(x, 11).Value <> 2 * base Then

If ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And [COLOR="#0000CD"][B](ActiveWorkbook.ActiveSheet.Cells(i, 11) <> base [U]Or[/U] ActiveWorkbook.ActiveSheet.Cells(i, 11) <> (2 * base))[/B][/COLOR] Then

                Select Case base
                    Case base, base * 2
                        MsgBox "SubTotal OK", vbOKOnly, "SubTotal OK"
                    Case Else

 

[Apple1]

Could we see a small set of sample data and have an explanation in words of what the code should be doing for that sample data?


Other comments:

The only Integer declaration in this line is rw, all the other variables will be Variant type. If you want them all to be declared as Integer, you need to specify each one
Dim k as Integer, i as Integer, j as Integer, ...



The blue parts in all of the following will always be True (unless possibly if base = 0 for the first two codes)

Hi

Thank you for the reply.

Attached is a small sample of my data:

Column / Row No.	1	2	3	4	5	6	7	8	9	10	11
1		2	L								10
2		2
3		2	L								20
4		2

<colgroup><col span="12"></colgroup><tbody>
</tbody>

Row 1 & 3 have "L" in Column 3, so the code should check the value for Column 11. If any of Column 11 value is not Base or Base*2 then the VBA code should alert me. For eg, if base =10 and Row 1, column 11 = 13.

Thank you

 

[Peter_SSs]

Attached is a small sample of my data:

Row 1 & 3 have "L" in Column 3, so the code should check the value for Column 11. If any of Column 11 value is not Base or Base*2 then the VBA code should alert me. For eg, if base =10 and Row 1, column 11 = 13.

OK, thanks. Try this modification of your code. the critical thing is that all the comparisons need to be And, not Or

Sub SubTotal_test()
  Dim msg As String
  Dim i As Long, base As Long, Last_Row As Long
  
  base = InputBox("State the number of items")
  Last_Row = Cells(Rows.Count, 2).End(xlUp).Row
  msg = "SubTotal OK"
  For i = Last_Row + 1 To 1 Step -1
    If Cells(i, 3).Value = "L" And Cells(i, 11).value <> base And Cells(i, 11).Value <> 2 * base Then
      msg = "There is an error with the SubTotal. Please change manually."
      Exit For
    End If
  Next i
  MsgBox msg
End Sub

 

[Apple1]

OK, thanks. Try this modification of your code. the critical thing is that all the comparisons need to be And, not Or

Sub SubTotal_test()
  Dim msg As String
  Dim i As Long, base As Long, Last_Row As Long
  
  base = InputBox("State the number of items")
  Last_Row = Cells(Rows.Count, 2).End(xlUp).Row
  msg = "SubTotal OK"
  For i = Last_Row + 1 To 1 Step -1
    If Cells(i, 3).Value = "L" And Cells(i, 11).value <> base And Cells(i, 11).Value <> 2 * base Then
      msg = "There is an error with the SubTotal. Please change manually."
      Exit For
    End If
  Next i
  MsgBox msg
End Sub

Hi

Thanks for the reply. But it doesn't work too. Thank you!