Bugs

[frostflower11]

I have some bugs in this code that I am having trouble fixing.
The StateSearch sub is supposed to get a state name from the user, call the FindState sub, then display a message if the state is listed in the workbook. The FindState sub searches for the name given by the user.
This is the code I have so far:

Sub StateSearch()
Dim state As String
state = InputBox("Enter a state you'd like to search for.", "State search")
Call FindState(state As String)
MsgBox "State was " & isFound, vbInformation
End Sub

Sub FindState(state As String)
Dim isFound As Boolean
For Each ws In ActiveWorkbook
If ws.Name = state Then
isFound = True
Exit For
Else
isFound = False
End If
Next
End Sub

 

[My Aswer Is This]

Try this:

Sub State_Check()
Application.ScreenUpdating = False
Dim i As Long
Dim x As Long
x = 0
Dim ans As String
ans = InputBox("Enter a state you'd like to search for")
    For i = 1 To Sheets.Count
    If Sheets(i).Name = ans Then
       x = x + 1
    End If
    Next
    If x > 0 Then MsgBox "Yes sheet  " & ans & "  Does exist" Else MsgBox "No sheet " & ans & "  does not exist"
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

To do it in a manner similar to the way you were trying, the main thing would be to turn the second sub into a Function.
There's a few other changes as well, but see if this also does what you want.

Sub StateSearch()
  Dim state As String
  
  state = InputBox("Enter a state you'd like to search for.", "State search")
  MsgBox "State was " & FindState(state), vbInformation
End Sub

Function FindState(state As String) As Boolean
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = state Then
            FindState = True
            Exit For
        End If
    Next
End Function

When posting code in the forum, please use code tags as it makes your code much easier to read & debug. My signature block below explains how.

 

[My Aswer Is This]

And why would someone want to have a function run inside a script? My script it looks like to me works just fine.

To do it in a manner similar to the way you were trying, the main thing would be to turn the second sub into a Function.
There's a few other changes as well, but see if this also does what you want.

Sub StateSearch()
  Dim state As String
  
  state = InputBox("Enter a state you'd like to search for.", "State search")
  MsgBox "State was " & FindState(state), vbInformation
End Sub

Function FindState(state As String) As Boolean
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = state Then
            FindState = True
            Exit For
        End If
    Next
End Function

When posting code in the forum, please use code tags as it makes your code much easier to read & debug. My signature block below explains how.

 

[MARK858]

And why would someone want to have a function run inside a script? My script it looks like to me works just fine.

So you can use it in multiple procedures without re-writing the code?
I don't think anyone has stated the code you posted didn't work there was just an alternative offered.

 

[Peter_SSs]

And why would someone want to have a function run inside a script?

It is a very common structure in coding and is useful for obtaining a result from various parts of a procedure, or even a number of different procedures, without repeating the base code.

In this case, I used it because the OP was trying to use a separate procedure to see if the sheet existed and asked about fixing the code. So that's what I did.

My script it looks like to me works just fine.

It seems that you have taken offence that I offered an alternative. I'm sorry if that is the case, but answering a thread first doesn't give you exclusivity to that thread.

I made no negative comment about your code nor suggest that it didn't work, but since you have raised the issue I will make a comment about it. The comment is not about whether the code works or not but about efficiency & tidy coding practice. If the workbook has 100 sheets and the name matches the first sheet, it seems pointless checking the other 99. Having made that comment then, I should offer an alternative of a single procedure that avoids such checking (& also identifies separately if the user cancels or closes the Input Box or simply does not enter anything in that box).

Sub StateSearch2()
  Dim state As String
  Dim isFound As Boolean
  
  state = InputBox("Enter a state you'd like to search for.", "State search")
  If Len(state) > 0 Then
    On Error Resume Next
    isFound = Not ActiveWorkbook.Worksheets(state) Is Nothing
    On Error GoTo 0
    MsgBox "State was " & isFound, vbInformation
  Else
    MsgBox "You didn't enter a name"
  End If
End Sub