can someone give me a little push here? I've tried all the breaks I can with this code and checked vars that have values in the immediate window and I can't seem to get why the worksheet object is not being set and transacted. It's been a while since I've written legacy VB like this, so I'm sure it's probably something very simple. Here is my code:
Public Sub search()
Dim s1 As String
Dim s2 As String
Dim ws As Worksheet
Dim r As Range
s1 = InputBox("Enter State Abbreviation (LOWERCASE LETTERS)")
If s1 = "" Then
MsgBox "No State Entered. Operation Aborted", vbCritical, "Error"
Exit Sub
End If
s2 = InputBox("Enter Name (LOWERCASE LETTERS)")
If s2 = "" Then
MsgBox "No Name Entered. Operation Aborted", vbCritical, "Error"
Exit Sub
Else
Select Case s1
Case "IA"
Set ws = ActiveWorkbook.Worksheets("IA")
Case "MN"
Set ws = ActiveWorkbook.Worksheets("MN")
Case "MO"
Set ws = ActiveWorkbook.Worksheets("MO")
Case "NE"
Set ws = ActiveWorkbook.Worksheets("NE")
Case "IL"
Set ws = ActiveWorkbook.Worksheets("IL")
Case "KS"
Set ws = ActiveWorkbook.Worksheets("KS")
Case "CO"
Set ws = ActiveWorkbook.Worksheets("CO")
End Select
For Each r In ws.Range("A2", ws.Range("A2").End(xlDown))
If Trim(r) = Trim(s2) Then
ActiveWorkbook.FollowHyperlink (r.Offset(0, 2))
End If
Next r
End If
End Sub
the code fails on the first line of the loop:
For Each r In ws.Range("A2", ws.Range("A2").End(xlDown))
every var returns a val in the immediate window if I break it on the error line except the worksheet object. Can someone spot my misstep? thank you so much.
Adam
Bookmarks