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

nuttycow13

New Member
Joined
Feb 6, 2012
Messages
47
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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:

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

New Member
Joined
Feb 6, 2012
Messages
47
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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:

Code:
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.
 
Last edited by a moderator:

Forum statistics

Threads
1,081,560
Messages
5,359,604
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top