• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting number of sheets inbetween two other sheets?

M

mgao77

New Member
Is possible to count the number a sheets between two other sheets?

For example, I have a "start" sheet and an "end" sheet. I just need to count how many sheets are inbetween those two for a model im building.

Thanks for any advice.

Best,
 
Many ways to do that. Here's one of them

Code: 
Sub SMC()

    Const strSheetStart As String = "Sheet3"
    Const strSheetEnd As String = "Sheet2"
    Dim sht As Object
    For Each sht In Sheets
        If lng Then
            lng = lng + 1
        End If
        If sht.Name = strSheetStart Then
            lng = lng + 1
        ElseIf sht.Name = strSheetEnd Then
            Exit For
        End If
    Next sht
    If lng >= 2 Then
        MsgBox "There is(are) " & lng - 2 & " sheet(s) in between sheets '" & strSheetStart & "' and '" & strSheetEnd & "'", vbOKOnly, ""
    Else
        MsgBox "Are you sure your sheet references are in order? Please check and try again.", vbOKOnly, ""
    End If
    
End Sub
 
Thanks Sam for going above and beyond to help me out.

This is a bit sophisticated for what im trying to do. Would it possible to just have the result of the sheet count show up in another sheet- say "check" Cells A10. It just needs to be a number.

Thanks so much, you are awesome.. I will no make sure to come back here for your support!
 
Thanks, sam, I figured it out using the following code:

Sub SMC()


Code: 
Sub SMC()
 
    Worksheets("check").Range("c5").Value = Worksheets("end").Index - Worksheets("start").Index - 1
   
End Sub
 
Hehe.... sometimes it looks ever so sophisticated for a moment, only to then turn up as quite stupid. Yes, much more elegant solution that.
 
Hi ,

Just as an alternative , you can use the following UDF :
Code: 
Function SMC(Optional p1 As String = "", Optional p2 As String = "")
    Application.Volatile
    Dim ind1  as Integer , ind2 as Integer
    p1 = IIf(p1 = "", ThisWorkbook.Sheets(1).Name, p1)
    p2 = IIf(p2 = "", ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name, p2)
 
    ind1 = Application.WorksheetFunction.Min(Sheets(p2).Index, Sheets(p1).Index)
    ind2 = Application.WorksheetFunction.Max(Sheets(p2).Index, Sheets(p1).Index)
 
    SMC = ind2 - ind1 - 1
End Function

This can be called in any of the following ways :

=SMC()
=SMC("Start sheet name")
=SMC(,"End sheet name")
=SMC("Start sheet name","End sheet name")

Narayan
 
Hello @mgao77,
Here is one more approach:

Assuming that the cell A1 on each of the sheets you are looking to check are not empty (i.e. they contain some / any value), you can use a formula like the following:
=COUNTA(Sheet1:Sheet4!A1)

Cheers,
Sajan.
 
Here's another shorter version

Code: 
Function SMC(Optional p1 As String, Optional p2 As String) As Long
    Application.Volatile True
    With ThisWorkbook
        If p1 = "" Then p1 = .Sheets(1).Name
        If p2 = "" Then p2 = .Sheets(.Sheets.Count).Name
        SMC = Abs(.Sheets(p2).Index - .Sheets(p1).Index) - 1
    End With
End Function
 
Abhi,

Application.Volatile--the default parameter is True--makes the function volatile, ie, whenever there is a change in any cell in the sheet, the formula will automatically recalculate itself. On the other hand, if you do not use Application.Volatile in a user defined function such as the one above, it will only calculate when there is a forced calculation in that cell, OR when the arguments passed in that cell changes.

Hope that's clear :)

In case you need more clarity, a simple google search or MSDN search will give you a clearer idea.
 
Hi Abhijeet ,

To add to what Sam has posted , in this procedure , the reason it has been added is this :

Suppose we have called the procedure SMC with two parameters , say "Sheet1" and "Sheet7" ; assuming that Sheet2 , Sheet3 ,..., Sheet6 are in between Sheet1 and Sheet7 , the function will return 5 as the result , which is the correct result.

Now suppose you move Sheet7 in between Sheet4 and Sheet5 ; obviously , now the number of sheets between Sheet1 and Sheet7 are 3 ( Sheet2 , Sheet3 and Sheet4 ) , but the function will continue to display 5 if the statement Application.Volatile is not included.

If the statement is included , then Excel will recognize that since an operation has taken place which has had an effect on the input to the function , and will recalculate the function , thus giving the correct result.

Narayan
 
You must log in or register to reply here.
Back
Top