Run time error 91

krissz

Board Regular
Joined
Feb 21, 2010
Messages
95
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

Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You haven't defined WS_Sht anywhere, possibly...

Code:
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
 
Upvote 0
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) ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top