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:

Private Sub TextBox13_AfterUpdate()
Dim FindR As Variant
Application.Visible = True
Worksheets("Sheet5").Visible = True
'Start searching from top of column and activate the cell containing lookup value entered into TextBox13
Worksheets("Sheet5").Select
Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
FindR = Cells.Find(What:=UserForm1.TextBox13.Text, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
'If TextBox13 value isn't found, advise user and fill textboxes with dashes
If FindR Is Nothing Then
MsgBox ("Not currently stored in database.")
TextBox12.Value = "-"
TextBox14.Value = "-"
TextBox15.Value = "-"
TextBox16.Value = "-"
TextBox19.Value = "-"
TextBox17.Value = "-"
TextBox18.Value = "-"
'If TextBox13 text is found, put details into textboxes on UserForm.
Else
'set the contents of text1 to the contents of the cell 1 to the left etc
TextBox12.Value = ActiveCell.Offset(0, 1).Value
TextBox14.Value = ActiveCell.Offset(0, 2).Value
TextBox15.Value = ActiveCell.Offset(0, 3).Value
TextBox16.Value = ActiveCell.Offset(0, 4).Value
TextBox19.Value = ActiveCell.Offset(0, 5).Value
TextBox17.Value = ActiveCell.Offset(0, 7).Value
TextBox18.Value = ActiveCell.Offset(0, 8).Value

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:

Private Sub TextBox13_AfterUpdate()
Application.Visible = True
Worksheets("Sheet5").Visible = True
'Start searching from top of column and activate the cell containing lookup value entered into TextBox13
Worksheets("Sheet5").Select
Range("A1").Select
Cells.FindNext(After:=ActiveCell).Activate
Cells.Find(What:=UserForm1.TextBox13.Text, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
'set the contents of text1 to the contents of the cell 1 to the left etc
TextBox12.Value = ActiveCell.Offset(0, 1).Value
TextBox14.Value = ActiveCell.Offset(0, 2).Value
TextBox15.Value = ActiveCell.Offset(0, 3).Value
TextBox16.Value = ActiveCell.Offset(0, 4).Value
TextBox19.Value = ActiveCell.Offset(0, 5).Value
TextBox17.Value = ActiveCell.Offset(0, 7).Value
TextBox18.Value = ActiveCell.Offset(0, 8).Value
End Sub

~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:

'use a With clause at the start eg
With Worksheets("sheet5")
    .Visible = True
    Application.Goto Reference:=.Range("a1")
End With


'Change 
Dim FindR As Variant
'to 
Dim FindR As range
'and change 
FindR = Cells.Find(What:=UserForm1.TextBox13.Text, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate

'to read 
set FindR = Cells.Find(What:=UserForm1.TextBox13.Text, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)

'once this is done you can change 
TextBox12.Value = ActiveCell.Offset(0, 1).Value
' & ...
'to read 
with FindR
TextBox12.Value = .Offset(0, 1).Value
'& ...
end with

'also your first post doesn't show the end of the sub & I assume that it
'actually states
end if
end sub

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

Cells.FindNext(After:=ActiveCell).Activate

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:

Private Sub TextBox13_AfterUpdate()
Dim FindR As Range
With Worksheets("sheet5")
    .Visible = True
    Application.Goto Reference:=.Range("a1")
End With
Set FindR = Cells.Find(What:=UserForm1.TextBox13.Text, After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If FindR Is Nothing Then
MsgBox ("Manager is not currently in database.")
TextBox12.Value = "-"
TextBox14.Value = "-"
TextBox15.Value = "-"
TextBox16.Value = "-"
TextBox19.Value = "-"
TextBox17.Value = "-"
TextBox18.Value = "-"
Else
With FindR
TextBox12.Value = ActiveCell.Offset(0, 1).Value
TextBox14.Value = ActiveCell.Offset(0, 2).Value
TextBox15.Value = ActiveCell.Offset(0, 3).Value
TextBox16.Value = ActiveCell.Offset(0, 4).Value
TextBox19.Value = ActiveCell.Offset(0, 5).Value
TextBox17.Value = ActiveCell.Offset(0, 7).Value
TextBox18.Value = ActiveCell.Offset(0, 8).Value
'Sheet5.Visible = xlVeryHidden (commented out to allow Forum users to have a gander!)
'Application.Visible = False (commented out to allow Forum users to have a gander!)
End With
End If
End Sub

~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