+ Reply to Thread
Results 1 to 11 of 11

New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

  1. #1
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    Hi, I have been using the following code for some time with no issue. Suddenly I am getting the error code mentioned in the post title. I can figure out why this would happen or what may have been changed to cause this error to start occurring, can anyone give me a clue where to look?? The following code is called from a userform with 15 sets of combo boxes that the user selects a date, shift time and shift end time from.

    Please Login or Register  to view this content.

    The debug highlights this line

    Please Login or Register  to view this content.
    which is just supposed to return a row number, in the debugger when I mouseover "DateColumn" it says that the value is zero. I'm not sure why this is.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    DateColumn will be 0 before you assign a value to it.

    The error is most likely caused by the Find failing which could be more to do with the data than the code.

    Has the data changed?
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    Thanks for the reply Norie, The data should all still be the same

    Please Login or Register  to view this content.
    sheet1 still has a range called ListDates and the combobox names haven't changed. I tried changing the Range reference to
    Please Login or Register  to view this content.
    To see if it would make a difference. I double checked that nothing has changed in that range. Its crazy but the first text after changing the an address instead of the named range, and the code worked. Then I tested it again and it failed. Any ideas? Is this a crazy date format issue?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    It could be a date formatting issue.

    How are you populating the comboboxes?

    Does it involve the range ListDates?

  5. #5
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    Like this...
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    So the comboboxes are populated from the range ListDates?

    If that's the case it might be possible to use the ListIndex property of the comboboxes to find the row for the selected date.

    I can't say for sure without any sample data.

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  7. #7
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    Book1.xlsx Here ya go

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    You should be able to get the Row like this.
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    Quote Originally Posted by Norie View Post
    You should be able to get the Row like this.
    Please Login or Register  to view this content.
    Hi Norie, That seems to work, I will mark as solved. What does ListIndex return? How does that work I mean?

  10. #10
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    btw thank you so much for helping me Norie Much appreciated

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: New Error/Old Code - Run Time Error 91; Object variable or With block variable not set

    ListIndex returns the position of the selected value in the combobox.

    If the combobox has been populated from a contiguous range, as it is in your case, you can use it to determine the row on the sheet where the value selected comes from.

    Since your named range starts in row 3, and because ListIndex is 0-based, we add 3 to get the row.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel VBA error code 91: Object variable or with block variable not set
    By wilnexpc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2013, 11:46 AM
  2. Replies: 0
    Last Post: 04-16-2013, 07:15 AM
  3. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  4. Replies: 1
    Last Post: 09-25-2012, 08:03 PM
  5. [SOLVED] Intermittent Run-time Error 91: Object Variable or With Block variable not set
    By fraanchtoast in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-29-2012, 10:11 AM

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