+ Reply to Thread
Results 1 to 3 of 3

countif in the last 5 sheets

Hybrid View

  1. #1
    Registered User
    Join Date
    12-02-2007
    Posts
    27

    countif in the last 5 sheets

    Hi

    I have a workbook that has 20 sheets.
    I need to count the number of 2 in column A of the last 5 sheets.
    Bearing in mind that whenever i run the macro the page name may change.

    here is what i have to do before counting the number of 2's in column A:

    If Sheets.Count > 4 Then
        For i = Sheets.Count - 4 To Sheets.Count
            If Not flg Then
                Sheets(i).Select     
                
            Columns("A:A").Select
            Selection.Insert Shift:=xlToRight
            Range("A2").Select
            ActiveCell.FormulaR1C1 = _
            "=IF(AND(ISERROR(RC[2]),RC[1]<>""""),1,IF(AND(ISERROR(RC[2]),RC[1]=""""),2,""""))"
    
      Else
                Sheets(i).Select False
            End If
        Next
    End If
    so can any1 help?

    thank you very much. You rhelp is much appreciated.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,591
    Sub test()
    Dim i As Integer, myCount As Long
    If Sheets.Count > 4 Then
        For i = Sheets.Count - 4 To Sheets.Count
            myCount = myCount + WorksheetFunction.CountIf(Sheets(i).Range("a:a"),2)
        Next
        MsgBox myCount
    End If
    End Sub

  3. #3
    Registered User
    Join Date
    12-02-2007
    Posts
    27

    thank you

    thank you so much
    i would like to know how do you know what VBA codes to input when you want to count or something?.

    is there any tutorial that i can read up?

    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1