run-time error 91 object variable or with block variable not set

[nuttycow13]

I am trying to get the userform to search column B depending on the user input in a text box and then populate text boxes in another userfrom from the cells found in the excel row. the code i have is:

Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range

' possibly use Set ws = Workshsets(Sitebox.Value)?

Select Case Sitebox.Value
Case "UKCH"
Set ws = Sheet2
Case "SDHQ"
Set ws = Sheet1
Case "NLEI"
Set ws = Sheet13
Case "USHW"
Set ws = Sheet10
Case "SGSG"
Set ws = Sheet11
End Select


Set rng = ws.Range("B1")

Do While rng.Value <> ""
If rng.Value = Find_Printer.Text Then
Found_Printer.Printer_Name.Value = rng.Offset(0, 2).Value
Found_Printer.Vaild.Value = rng.Offset(0, 18).Value
Found_Printer.DNS.Value = rng.Offset(0, 14).Value
Found_Printer.IP.Value = rng.Offset(0, 12).Value
Found_Printer.Found_Patch.Value = rng.Offset(0, 7).Value
Found_Printer.Found_Fax.Value = rng.Offset(0, 10).Value
Found_Printer.Found_Serial.Value = rng.Offset(0, 6).Value
Found_Printer.Found_Model.Value = rng.Offset(0, 5).Value
Found_Printer.Found_Make.Value = rng.Offset(0, 4).Value
Found_Printer.Found_Wing.Value = rng.Offset(0, 8).Value
Found_Printer.Found_Mac.Value = rng.Offset(0, 11).Value
Found_Printer.Host.Value = rng.Offset(0, 12).Value
Found_Printer.GIS.Value = rng.Offset(0, 17).Value
Found_Printer.Comments.Value = rng.Offset(0, 19).Value
Found_Printer.Found_Type.Value = rng.Offset(0, 3).Value
Found_Printer.Found_Location.Value = rng.Offset(0, 9).Value
Found_Printer.Found_Site.Value = Sitebox.Value
Found_Printer.Found_Number.Value = Numberbox.Value
End If

Set rng = rng.Offset(1)
Loop

Found_Printer.Show vbModal

End Sub

The Bold section is where i get the error message:

run-time error 91 object variable or with block variable not set

can any one help me with this please its really starting to wind me up.

Thanks in Advance

 

[Firefly2012]

I reckon the problem lies with variable ws - it is this that is nothing, so you are tryin g to reference a range on a worksheet that doesn't exist.

What would you intend to happen if none of the following in your Select Case aply:

Select Case Sitebox.Value
Case "UKCH"
Set ws = Sheet2
Case "SDHQ"
Set ws = Sheet1
Case "NLEI"
Set ws = Sheet13
Case "USHW"
Set ws = Sheet10
Case "SGSG"
Set ws = Sheet11
End Select

 

[nuttycow13]

ok well when i run it i can get to the stage of selecting the site, so i do that and select one of the sites listed and then its when i get to the stage of ****ting the find button it all goes wrong, if no site was entered i would like nothing to be done as without the site the full name can not be generated.

I hope its clear what i mean.

 

[Firefly2012]

If the SiteBox.Value should contain the name of the actual sheet, then you should use your commented out line of code, suitable amended to handle instances where an invalid or blank had been input:

On Error Resume Next
Set ws = Sheets(Sitebox.Value)
On Error Goto 0
 
If ws Is Nothing Then MsgBox "Invalid sheet input! Exiting Sub": Exit Sub
 
'rest of code

This replaces your Select Case structure.

 

[nuttycow13]

Bingo !! Thank you very much that worked you are truly a master.