Looping withing worksheets using AND condition

Question

I have a VBA macro to check the existing 10 worksheets in the workbook and perform an analysis as shown below. I want to loop this code to do the analysis for 100 worksheets using For loop. I am stuck on how to combine AND condition and for loop? Help will be appreciated.

I have tried to use the for loop but failed.

Sub Mostoccurence()
'
' Mostoccurence Macro
'

'
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet11").Select
    Sheets("Sheet11").Name = "analysis"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=AND(Sheet1!RC=1,Sheet2!RC=1,Sheet3!RC=1,Sheet4!RC=1,Sheet5!RC=1,Sheet6!RC=1,Sheet7!RC=1,Sheet8!RC=1,Sheet9!RC=1,Sheet10!RC=1)"
End Sub

Use of Select is not ideal in VBA. [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Mikku, Jul 30 '19 at 07:46

score 0 · Answer 1 · answered Jul 30 '19 at 08:07

Try something like this:

Option Explicit

Sub AddSheetCompare()
    Dim oAnalysisSht As Worksheet
    Dim oLoopSht As Worksheet
    Dim lRow As Long
    On Error Resume Next
    Set oAnalysisSht = Worksheets("Analysis")
    On Error GoTo 0
    If oAnalysisSht Is Nothing Then
        Set oAnalysisSht = ActiveWorkbook.Worksheets.Add(ActiveWorkbook.Worksheets(1))
        oAnalysisSht.Name = "Analysis"
        oAnalysisSht.Range("A1").Value = "Result of all worksheets"
        oAnalysisSht.Range("A3").Value = "Worksheet Results"
    End If
    lRow = 3
    For Each oLoopSht In Worksheets
        If Not oLoopSht.Name = oAnalysisSht.Name Then
            lRow = lRow + 1
            oAnalysisSht.Range("A" & lRow).Formula = "='" & oLoopSht.Name & "'!A1=1"
        End If
    Next
    oAnalysisSht.Range("A2").Formula = "=AND(A4:A" & lRow & ")"
End Sub

Looping withing worksheets using AND condition

1 Answers1