Non-Stop Compiling Errors!

**LiamPotter** · 03-13-2008, 09:28 AM

Hi there,

Many thanks for anyone's response which will help be quickly clear up my situation. I have a Userform on an Excel spreadsheet which consists of two Multipages (the main one and a sub one which isn't related to this query).

I have one of the multipages currently being used as a Lookup function. When you type the lookup value (as you would with standard VLOOKUP) it returns the 7 values I require into another 7 textboxes. The code I had worked perfectly until I added code to enable a minimize/maximize button on the Userform and added a function to inform the user of an un-returned value because it is not stored in the database.

The code I current have is the following:

Please Login or Register  to view this content.

The TextBox and ActiveCell.Offset's are definitely correct so they do not need changed however I am still dumbfounded as to why I am receiving a Run Time Error 91 (Object Variable or With block variable not set) error where it then higlights 'Cells.FindNext(After:=ActiveCell).Activate'

The lookup works fine if I take out IF function part for informing the user the value was not returned but this is an option I HAVE to have built in.

Does anyone know where I am going wrong?

~Liam

**Leith Ross** · 03-13-2008, 12:02 PM

Hello Liam,

Welcome to the Forum!

Due to the complexity of your layout, it would be faster to troubleshoot it if you can post your workbook as an attachment. The file must be zipped and can not be more than 100 kb after it is zipped.

Sincerely,
Leith Ross

**LiamPotter** · 03-13-2008, 12:13 PM

Unfortunately I wouldn't be able to attach the workbook - even zipped the file is > 500kb itself and contains information on thousands of clients. I'm also at work at the moment and my company doesn't have a zipping facility.

I'd be more than willing to provide any information in relation to the code involved however none of the other functions of the Userform are applicable.

The only one I'm having a problem with is the above code. The code worked perfectly until I tried to add in a way of advising the user an option they had searched for was not contained in the database (which is on Sheet5) and filled the TextBox's, as above, with dashes (-).

All I'm really asking is either how to fix my code above (as knowing my luck there's a tiny error with it), or for an alternative way to advise the user and fill in the boxes if their result is not in the database.

EDIT - FYI the code runs successfully without the option to advise the user etc, the code of which is shown below:

Please Login or Register  to view this content.

~Liam

**LiamPotter** · 03-14-2008, 04:43 AM

I apologise if this isn't allowed but I'm bumping my thread.

I'm literally tearing my hair out with this and can't work out why on earth the code doesn't work.

Any ideas?

EDIT - Please find attached a copy of the workbook. I've removed most of the information you didn't need to know (to enable to you have a look through), removed password details, commented out sheet hiding and removed the local file pictures from the userform etc. The one I'm having a problem with is as above.

~Liam

**broro183** · 03-14-2008, 06:10 AM

hi

Here are a couple of changes which make your code slightly more efficient & may help:

Please Login or Register  to view this content.

The above may help but I think the main issue is your use of "Findnext"...

Please Login or Register  to view this content.

The Excel 2003 Help Files state

Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell.

I think that you/the code has some unknown effect (to me & you, anyway) of clearing an existing Search. Can you delete this line of code completely & just rely on the "Cells.find..." line of code?

hth
Rob

**LiamPotter** · 03-14-2008, 06:27 AM

Thank you very much for your help, much appreciated.

I've managed to edit the code as per your post above however if I try to use the lookup now it simply gives me the "Manager not currently in database" message even if the manager definitely is in the database and fills the TextBox's with dashes (-).

This means my If function isn't working now but again I can't see how. The current code is as follows:

Please Login or Register  to view this content.

~Liam

**broro183** · 03-14-2008, 07:02 AM

hi Liam,

I'm not sure what is going wrong, I've tried it by updating the code in your original zip file & it seems to work for me (the only tweak I think I made was to remove "activecell...." on the last section of the macro).

I used "Aberdeen" as an example & changed the values on sheet 5 to make it easier to tell when it was working. I suggest using pressing [F9] on the first with clause to create a break point so you can go through the code line by line & hold your mouse over the "FindR" once you've set it, to see if it has a value.

Sorry I haven't any other suggestions to offer & am off to bed now but I will look again at some stage tomorrow.

Rob

**LiamPotter** · 03-14-2008, 07:44 AM

For some reason it still doesn't work (even your attached file). Could it be a different code because of the VBA version I am using (6.3)?

~Liam

**broro183** · 03-17-2008, 01:48 AM

hi Liam,

Sorry I've taken so long to come back to you.

I'm not sure exactly what caused the problem but I don't think it relates to a different version of VBA.
In the below code I've changed "cells..." to ".cells..." to ensure it relates to sheet 5 & have removed the reference to the Activecell in the Find statement. Does the below work?

Please Login or Register  to view this content.

Also, I suggest:
1) renaming your sheets to have meaningful names, although it will take a bit of time to correct the references in your macros, it will allow for easier understanding/maintenance etc if you look at it 6 months down the track.
2) Increase your use of With statements wherever you have code referring to the same object/range on 2 or more (consecutive/ can be made that way) lines (this can increase the speed of your macro).

hth
Rob

**LiamPotter** · 03-17-2008, 04:38 AM

That works brilliantly, thank you very much for your help. I've had to remove the Stop reference as this halts the code and causes an error - was there any reason this was included?

~Liam

**broro183** · 03-18-2008, 03:58 AM

Ooopps!
I forgot to take it out, the stop line was in there to let me see what was happening as I tested the code when I opened the workbook & typed in the form.

I'm pleased I could help - thanks for the feedback :-)

Rob

Non-Stop Compiling Errors!

LinkBack

Thread Tools

Rate This Thread

Display

Non-Stop Compiling Errors!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions