0

Need help, If statement not providing desired result. Values in the comparing cells are the same but the if argument works half the time. Code provided below

Sub autofilter1()


For b = 1 To 4
' Last row of unique values - Unique Tab
lr = Sheets("Unique").Cells(Rows.Count, b).End(xlUp).Row

    'Tabs = c
    ws_count = ActiveWorkbook.Worksheets.Count
    For c = 2 To ws_count
        'Last row of column A
        lr1 = Sheets(c).Cells(Rows.Count, 1).End(xlUp).Row

        For i = 2 To lr
            'Execution of auto filter program
            Sheets(c).Range("A1:A" & lr1).autofilter Field:=1, Criteria1:=Sheets("Unique").Range("A" & i)
            'Last row of Filtered visible cells
            lr2 = Sheets(c).Cells(Rows.Count, 4).End(xlUp).Row

            'Below line selects entire range of visible cells
            'Sheets("Assets").Range("D2:D" & lr2).SpecialCells(xlCellTypeVisible).Select

                'Selection of Cell to identify aggregate address 1) Range definition, 2) sub-class aggregate cell identifier
                With Sheets(c).autofilter.Range
                    Range("D" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
                End With

            'Dynamic Sum function home
            Selection.Offset(0, 1).Select
            'First cell below primary (only in case of multiple sub accounts, else primary is account)
            SS = Selection.Offset(1, -1).Address
            'Final cell of dynamic autofilter range
            SE = ("D" & lr2)
            Rng = Sheets(c).Range(SS & " : " & SE).SpecialCells(xlCellTypeVisible)

            ActiveCell = Application.WorksheetFunction.Sum(Rng)

                  If ActiveCell.Value = ActiveCell.Offset(0, -1).Value Then
                       ActiveCell.Offset(0, 1) = "True"
                       ActiveCell.Offset(0, 1).Font.Bold = True
                       ActiveCell.Offset(0, 1).Interior.Color = 5296274
                  Else
                        ActiveCell.Offset(0, 1) = "False"
                        ActiveCell.Offset(0, 1).Font.Bold = True
                        ActiveCell.Offset(0, 1).Interior.Color = 255
            End If
        Next i

    Next c

Next b

End Sub
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Rahul Singh
  • 1
  • 1
    "Works half the time" is what eventually happens with code written against `Selection` and `ActiveCell` and implicit `ActiveSheet` references. `With Sheets(c).autofilter.Range` is qualifying a `.Offset` member call, but the preceding `Range` is unqualified, making it unlikely to have the expected effect unless `Sheets(c)` happens to be active. Start with extracting `Sheets(c)` into a local `Worksheet` variable, and dereference the object from the `ActiveWorkbook.Worksheets` collection - your current code will break if a chart sheet is inserted at or after index `2`, for example. Good luck! – Mathieu Guindon Apr 26 '18 at 01:17
  • This might be of interest: https://stackoverflow.com/q/10714251/1188513 – Mathieu Guindon Apr 26 '18 at 01:20
  • I see what you mean, the code broke multiple times. Will get back with an updated code. thanks a lot. – Rahul Singh Apr 26 '18 at 01:21

0 Answers0