<> don't work

[Apple1]

Hi

For some reason, the below code don't work. It shows the error message even when all my filtered rows =base

Thank you

Option Explicit
 
Sub Macro1()
'
' Macro1 Macro
'
 
'
 
Dim base As Integer
 
Dim c As Range
 
 
 
base = InputBox("State base")
 
ActiveWorkbook.ActiveSheet.Range("A:W").AutoFilter Field:=11, Criteria1:="<>" & 2 * base
 
For Each c In ActiveWorkbook.ActiveSheet.Range("K:K").SpecialCells(xlCellTypeVisible)
 
       If c.Value <> base Then
            Cells(1, 1000) = "ERROR"
            MsgBox "There is an error with the SubTotal. Please change manually."
       Exit For
 
       End If
 
Next
End Sub

 

[mumps]

Try:

Option Explicit
Sub Macro1()
    Application.ScreenUpdating = False
    Dim bottomK As Long
    bottomK = Range("K" & Rows.Count).End(xlUp).Row
    Dim base As Integer
    Dim c As Range
    base = InputBox("State base")
    ActiveWorkbook.ActiveSheet.Range("A:W").AutoFilter Field:=11, Criteria1:="<>" & 2 * base
    For Each c In ActiveWorkbook.ActiveSheet.Range("K2:K" & bottomK).SpecialCells(xlCellTypeVisible)
           If c.Value <> base Then
                Cells(1, 1000) = "ERROR"
                MsgBox "There is an error with the SubTotal. Please change manually."
                Exit For
           End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Just a nit comment. ActiveWorkbook.ActiveSheet is a redundant expression since ActiveSheet would have to be in the active workbook. The code would work just as well if ActiveWorkbook was omitted.

 

[Fluff]

Just a nit comment. ActiveWorkbook.ActiveSheet is a redundant expression since ActiveSheet would have to be in the active workbook. The code would work just as well if ActiveWorkbook was omitted.

You could also get rid of the ActiveSheet as well

 

[mumps]

@Fluff, @JLGWhiz: Thank you. Should have picked up on that.

 

[JLGWhiz]

@Fluff, @JLGWhiz: Thank you. Should have picked up on that.

Agree that it wilol work in this case without the ActiveSheet, but it is good practice to always qualify a range object with the parent sheet so that when more than one sheet is referred to in the code, there is no accidental default to the wrong sheet.