VBA: Alert if Base or 2*Base

A

Apple1

Board Regular
Joined
Jan 18, 2015
Messages
121
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try:
Code: 
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
 
Last edited:
Upvote 0
change your if like this:

Code: 
If ActiveWorkbook.ActiveSheet.Cells(i, 3) = "L" And (ActiveWorkbook.ActiveSheet.Cells(i, 11) <> base Or ActiveWorkbook.ActiveSheet.Cells(i, 11) <> (2 * base)) Then
 
Upvote 0
amiroo said:
change your if like this:

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


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
 
Upvote 0
Try:
Code: 
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
 
Last edited:
Upvote 0
JackDanIce said:
Try:
Code: 
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
Click to expand...

Thank you for the reply. But it still doesn't work. I am not sure why.
 
Upvote 0
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
Click to expand...
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)
JackDanIce said:
Rich (BB code): 
                If .Cells(x, 11).Value <> base Or .Cells(x, 11).Value <> 2 * base Then
Click to expand...
amiroo said:
Code: 
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
Click to expand...
JackDanIce said:
Rich (BB code): 
                Select Case base
                    Case base, base * 2
                        MsgBox "SubTotal OK", vbOKOnly, "SubTotal OK"
                    Case Else
Click to expand...
 
Upvote 0
Peter_SSs said:
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)
Click to expand...

Hi

Thank you for the reply.

Attached is a small sample of my data:

[TABLE="width: 768"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD]Column / Row No.
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]L
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]L
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
Apple1 said:
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.
Click to expand...
OK, thanks. Try this modification of your code. the critical thing is that all the comparisons need to be And, not Or
Rich (BB code): 
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
 
Upvote 0
Peter_SSs said:
OK, thanks. Try this modification of your code. the critical thing is that all the comparisons need to be And, not Or
Rich (BB code): 
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
Click to expand...

Hi

Thanks for the reply. But it doesn't work too. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
230
igold
igold
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
134
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Macro To Move Files - Will Not Move .msg or .csv files
Replies
4
Views
82
rachel06
R
S
  • Question Question
Formula for Pulling Data From Sheet 2 Based on Cell Value in Sheet 1
Replies
1
Views
168
Kaper
Kaper
P
  • Question Question
DeferredDeliveryTime help
Replies
1
Views
712
dreid1011
dreid1011

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top