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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,684
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,155
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,684
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,684
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 

Forum statistics

Threads
1,181,615
Messages
5,930,962
Members
436,767
Latest member
Langaws

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
Top