Results 1 to 8 of 8

object var or with block var not set

Threaded View

  1. #1
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    object var or with block var not set

    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
    Last edited by vba_php; 08-06-2020 at 01:55 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Object or With Block Error
    By 5150 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-19-2014, 11:20 AM
  2. macro block object
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-20-2013, 12:57 PM
  3. [SOLVED] Object Variable or with block not set
    By KeithMale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-18-2013, 04:46 PM
  4. [SOLVED] Object Variable/With Block Not Set When Naming Ranges
    By sperry2565 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2013, 12:59 PM
  5. [SOLVED] Object Variable Or With Block Not Set
    By AlexRoberts in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-28-2011, 06:27 AM
  6. Error #91 Object Variable or With Block Not Set
    By t0m46 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2010, 12:07 PM
  7. [SOLVED] object variable or with block not set
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2006, 04:45 AM
  8. Object variable or with block not set
    By Benny5788 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1