+ Reply to Thread
Results 1 to 8 of 8

Run-Time error '91' - Object Variable or With block variable not set

  1. #1
    Registered User
    Join Date
    08-22-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    81

    Run-Time error '91' - Object Variable or With block variable not set

    Hi everyone

    i have this section of code that end in a Run Time error 91

    the weird thing is i can sometimes get it to run with no error... :S

    it used to work fine and now its giving me the error, where am i going wrong.




    Please Login or Register  to view this content.
    what im trying to do is filter the excel sheet, down to what is stored in a text box.
    then because the sheet is filtered i need to know what the last row is that has data in it.
    then i want to save data from J2 all the way down to the last row in column J.
    there is more code after this but its the filtering and finding what the last row number is thats not working.

    sometimes the code works and sometimes i get a run time error 91.

    i dont understand why sometimes it will work.

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

    Re: Run-Time error '91' - Object Variable or With block variable not set

    The reason for the error is that Find isn't finding what you are looking for when that happens Nothing is returned.

    Where exactly are you looking for strLastVal?
    If posting code please use code tags, see here.

  3. #3
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Run-Time error '91' - Object Variable or With block variable not set

    LastRow = Cells.Find(What:=strLastVal, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    not sure if your direction needs to be xlNext
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  4. #4
    Registered User
    Join Date
    08-22-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    81

    Re: Run-Time error '91' - Object Variable or With block variable not set

    I thought that might be the case noire.

    Strlastval is what's in a text box.

    And What is in the textbox is in a cell.

    Srtlastval has a * added to the end. Because I want to find not the exact result but just where a cell starts with what is in the text box.

    Cheers

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

    Re: Run-Time error '91' - Object Variable or With block variable not set

    Can you upload an example workbook?

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

  6. #6
    Registered User
    Join Date
    08-22-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    81

    Re: Run-Time error '91' - Object Variable or With block variable not set

    Give me a moment Norie, the excel is over 17mb large i will need to remove all the data.

    Cheers

  7. #7
    Registered User
    Join Date
    08-22-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    81

    Re: Run-Time error '91' - Object Variable or With block variable not set

    Hi Norie - it wont let me upload as the file is 8.9mb at its smallest, i sent you a private message

  8. #8
    Registered User
    Join Date
    08-22-2014
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    81

    Re: Run-Time error '91' - Object Variable or With block variable not set

    I have solved this issue now. and i now know why it would only sometimes work and sometimes not work causing the error. However i don't fully understand why my work around solves the issue.

    on my form i had a list box, and when you selected something in the listbox it would find a match on the excel spreadsheet. and move to that match. however when i was running my subroutine it was without making any selection at all in the list box. this was causing the error. so long as my listbox has a row selected in it, it works.

    so i have added a line of code

    listbox1.listindex = 0

    so that when you click the save button, a selection is made in the listbox via VBA code.

    mind you the LastRow = code i was running had nothing directly to do with the listbox, i can only assume the reason this makes it work is because on change of the listbox, it would just perform the VBA search cells for command with the criteria of what the current listbox1.value was and this somehow interferes with storing the value into my variable for the LastRow = command.

    But many thanks to those who helped.

+ 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. Replies: 0
    Last Post: 04-16-2013, 07:15 AM
  2. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  3. Replies: 1
    Last Post: 09-25-2012, 08:03 PM
  4. [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
  5. Run-Time error '91': Object variable of With block variable not set
    By jammin1911 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2006, 01:36 PM

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