Run time error 91

[krissz]

I am developing a spreadsheet for analysing expenses & other costs.
The data will be in one of the tabs, the full name of which is not known but we know that the name starts with a known string - in this case "statement".
What I want is the end of the name: F_Nam.
What have I done wrong ? I get error 91 messages

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim WS_Name As String
         Dim WS_Sht As Worksheet
         Dim F_Nam As String
         Dim I As Integer

         WS_Count = ActiveWorkbook.Worksheets.Count

         For I = 1 To WS_Count
         ActiveWorkbook.Worksheets(I).Activate
            If Mid(WS_Sht.Name, 1, 9) = "statement" Then
               F_Nam = Mid(WS_Sht.Name, 10, 6)
               MsgBox F_Nam
            End If
         Next I
End Sub

 

[MARK858]

You haven't defined WS_Sht anywhere, possibly...

Sub WorksheetLoop()

         Dim WS_Count As Long
         Dim WS_Name As String
         Dim WS_Sht As Worksheet
         Dim F_Nam As String
         Dim I As Long

         WS_Count = ActiveWorkbook.Worksheets.Count

         For I = 1 To WS_Count
         Set WS_Sht = ActiveWorkbook.Worksheets(I)
            If Mid(WS_Sht.Name, 1, 9) = "statement" Then
               F_Nam = Mid(WS_Sht.Name, 10, 6)
               MsgBox F_Nam
            End If
         Next I
End Sub

 

[krissz]

Thanks
WS_Sht is defined as worksheet; 3rd line

 

[GTO]

Thanks
WS_Sht is defined as worksheet; 3rd line

Well... That's true, but MARK858 meant you have not reference any particular sheet to WS_Sht. See where he has Set WS_Sht = ActiveWorkbook.Worksheets(I) ?

 

[MARK858]

Thanks
WS_Sht is defined as worksheet; 3rd line

That is declaring WS_Sht as a variable not defining the variable.
Defining the variable is where you are assigning the worksheet to the variable (or not in your case).

As GTO has stated..

See where he has Set WS_Sht = ActiveWorkbook.Worksheets(I) ?

.

What you haven't stated is does the code in post #2 work?

 

[krissz]

Many thanks. I missed that line.
I blame it on my eyes - had cataract surgery on one eye which is now perfect while the other is badly short sighted.
Use both eyes as otherwise the bottle may miss the glass

 

[MARK858]

I know the feeling, I had congenital cataracts and had my eyes operated on when I was 13 and 15 (before the days the days of laser surgery).

Which is fine until my contacts dry out when I leave them in too long

 

[krissz]

Thanks to everybody for the help
Works perfectly