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

 

[Peter_SSs]

But it doesn't work too.

That doesn't give a helper much to go on.
Did it do nothing?
Did it error?
Did it say "SubTotal OK" when it shouldn't have?
Did it say "There is an error with the SubTotal. Please change manually." when it shouldn't have?

Here is my sample sheet using your data from post 8.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		2	L								10
2		2
3		2	L								20
4		2
5
Check Base

When I ran the code and entered 10, I got the message "SubTotal OK"
When I ran the code and entered 6, I got the message "There is an error with the SubTotal. Please change manually."

- Do I have the layout right? If not, more details please.
- Are those the expected messages for the data and the input values I gave? If not, more details please.

 

[Apple1]

That doesn't give a helper much to go on.
Did it do nothing?
Did it error?
Did it say "SubTotal OK" when it shouldn't have?
Did it say "There is an error with the SubTotal. Please change manually." when it shouldn't have?

Here is my sample sheet using your data from post 8.

	A	B	C	D	E	F	G	H	I	J	K	L
L
L

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Check Base​

When I ran the code and entered 10, I got the message "SubTotal OK"
When I ran the code and entered 6, I got the message "There is an error with the SubTotal. Please change manually."

- Do I have the layout right? If not, more details please.
- Are those the expected messages for the data and the input values I gave? If not, more details please.

Hi

Thank you so much! It does work. I forgot to dim i as integer.

Thank you again!

 

[Peter_SSs]

Hi

Thank you so much! It does work. I forgot to dim i as integer.

Thank you again!

Sounds like you didn't actually use my code then as I had declared all my variables.

You will note that I didn't declare any variables as Integer. The reason is that vba converts all Integer variables to Long before it uses them, so why not save that conversion and just start with Long? Besides, it's shorter to type Long.

Anyway, glad you got it working.