run-time error 438 object does not support this property or method

[nuttycow13]

I have been getting this error: "run-time error 438 object does not support this property or method " when trying to get a click button to search a spread sheet find a value from the entered text box and then read the cells and input them into text boxes in another userform.

this is the code i have:

Private Sub Printer_Find_Click()

If Sitebox.Value = "UKCH" Then
Sheet2.Activate
ElseIf Sitebox.Value = "SDHQ" Then
Sheet1.Activate
ElseIf Sitebox.Value = "NLEI" Then
Sheet13.Activate
ElseIf Sitebox.Value = "USHW" Then
Sheet10.Activate
ElseIf Sitebox.Value = "SGSG" Then
Sheet11.Activate
End If

Range("B1").Avtive
Do While ActiveCell.Value <> ""
If ActiveCell.Value = Find_Printer.Text Then
Found_Printer_Name.Value = Cells(0, 2).Value
Found_Vaild.Value = Cells(0, 18).Value
Found_DNS.Value = Cells(0, 14).Value
Found_IP.Value = Cells(0, 12).Value
Found_Patch.Value = Cells(0, 7).Value
Found_Fax.Value = Cells(0, 10).Value
Found_Serial.Value = Cells(0, 6).Value
Found_Model.Value = Cells(0, 5).Value
Found_Make.Value = Cells(0, 4).Value
Found_Wing.Value = Cells(0, 8).Value
Found_Mac.Value = Cells(0, 11).Value
Found_Host.Value = Cells(0, 12).Value
Found_GIS.Value = Cells(0, 17).Value
Found_Comments.Value = Cells(0, 19).Value
Found_Type.Value = Cells(0, 3).Value
Found_Location.Value = Cells(0, 9).Value
Found_Site.Value = Sitebox.Value
Found_Number.Value = Numberbox.Value


End If

ActiveCell.Offset(1, 0).Activate
Loop

Unload Me

Found_Printer.Show vbModal

End Sub

it would be great if anyone could help me. thanks in advance

 

[Norie]

How many printers are there?

I'm pretty sure it's not too many to display in a combobox or listbox.

You can even have a combobox that lists the sites and can be used as a filter.

If you did have the printers listed then it would be straightforward to fill textboxes based on the selected printer.

 

[nuttycow13]

i have a worked sheet with around 79 printers at the mo but it can go upto 9999 this is generated by a userform to add the printer details. and what it dose is find the first blank cell take the number and produce the printer name then add it to the work sheet.

I also have a delete function to remove a printer name if the printer is removed and then let the number be used agian.

what i need is a search function that will search this worksheet and find the printer name even if there is one cell that is blank in the middle. it works output the information to the textboxes no problem but its will only work if all the cells are filled. but as a printer could be deleted and a gap in the cells produced i need to find away of searching all the cells with a value in it.

if you look here you will see the whole work book i have got so far this will make more scene then i think

http://www.box.com/s/tg7y1he5jsh2qalyoyd1

 

[Norie]

Why not just delete the entire row when you delete a printer?

Or use Find.

By the way, you can use code for something like a listbox to filter/search.

I've already mentioned using a combobox to filter by site and there's no reason you can't add other filtering/searching functionality.

 

[nuttycow13]

i dont want to delete the entire row i want to take all the information from column B onwards and place it into another worksheet of deleted printers. as column A has the numbers for the printers i can not take the whole row.

And you say use the Find function ?? how does this work ?

 

[Norie]

Deleting the row doesn't stop you moving the data to another worksheet.

What is this number you speak of?

Is it significant in some way?

What exactly happens when a blank is encountered?

Using Find in code is just like using it manually.

 

[nuttycow13]

ok the number i speak of is column A its runs from 1 to 9999 and when adding the printer the code finds the last printer name in column B and then goes to the empty cell and moves then to column A and reads the number it then uses 3 text box inputs and the cell number input to produce the full printer name.

i dont understand how this find function would work within my code and how it works ?? would it find the printer name even with gaps in the rows with empty cells ??

you say deleting the row would not stop me moving the data to another worksheet which is fine. i do have this working if i can get the search to work with gaps in the cell. but with deleting the row in the way you are talking about would this delete the whole row including the information in cell A ?

Thanks for this help as i am pulling my hair out thought it was all working until i tried to do a different number search after a blank cell. so thanks again for the help.

 

[Norie]

You still don't say what's happening when there is a blank or what relevance the numbers in column A have.

Find will search for whatever you tell it to search in whatever range you tell it to search in.

Try turning on the macro recorder and manually searching for a printer.

The code produced should give you an idea of how Find works and how you might be able to use it.

 

[nuttycow13]

when there is a blank when i am trying to find a printer name the userform just comes back blank like there is no printer with that name.

The Column A number field has relevance with the add printer section. I could search for the printer by the number field in A but when i change the code to:

Private Sub Printer_Find_Click()
Dim ws As Worksheet
Dim rng As Range
 
  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
 

    Set rng = ws.Range("A1")
 
    Do While rng.Value <> ""
        If rng.Value = numberbox.Value Then
            Find_Printer.Value = rng.Offset(0, 0).Value
            Vaild.Value = rng.Offset(0, 16).Value
            DNS.Value = rng.Offset(0, 12).Value
            IP.Value = rng.Offset(0, 10).Value
            Patch.Value = rng.Offset(0, 5).Value
            Fax.Value = rng.Offset(0, 8).Value
            Serial.Value = rng.Offset(0, 4).Value
            Model.Value = rng.Offset(0, 3).Value
            Make.Value = rng.Offset(0, 2).Value
            Wing.Value = rng.Offset(0, 6).Value
            Mac.Value = rng.Offset(0, 9).Value
            Host.Value = rng.Offset(0, 11).Value
            GIS.Value = rng.Offset(0, 15).Value
            Comments.Value = rng.Offset(0, 17).Value
            Printer_Type_box.Value = rng.Offset(0, 1).Value
            Locationbox.Value = rng.Offset(0, 7).Value
            
      End If
 
        Set rng = rng.Offset(1)
    Loop
    
End Sub

Nothing happens i get no results from my search i am really confused now.

 

[Norie]

Try looping through all the data.

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
 
For I = 2 To LastRow 
     Set rng = ws.Range("B" & I)
 
     If rng.Value = numberbox.Value Then
         ' code to populate textboxes on userform
     End If
Next I

By the way I still don't see what the number is actually for and why it can't be deleted.

Just had another look at the file and the number just seems to be the row number minus one.

Is it that when you add new printer it's number is the last printer's number plus one?

Perhaps I'm just being thick but I can't really see the purpose of the number.

 

[nuttycow13]

thanks i have tried that code and it will find the details i am looking for but using my old code to populate the text boxes is now not working.

 Find_Printer.Value = rng.Offset(0, 0).Value
            Vaild.Value = rng.Offset(0, 16).Value
            DNS.Value = rng.Offset(0, 12).Value
            IP.Value = rng.Offset(0, 10).Value
            Patch.Value = rng.Offset(0, 5).Value
            Fax.Value = rng.Offset(0, 8).Value
            Serial.Value = rng.Offset(0, 4).Value
            Model.Value = rng.Offset(0, 3).Value
            Make.Value = rng.Offset(0, 2).Value
            Wing.Value = rng.Offset(0, 6).Value
            Mac.Value = rng.Offset(0, 9).Value
            Host.Value = rng.Offset(0, 11).Value
            GIS.Value = rng.Offset(0, 15).Value
            Comments.Value = rng.Offset(0, 17).Value
            Printer_Type_box.Value = rng.Offset(0, 1).Value
            Locationbox.Value = rng.Offset(0, 7).Value

have you any ideas why ? i placed this inside your code you showed me.

and as for the numbers it works by each location starting a 01 and running up by 1 each time which is fine but when ppl delete a printer they never seem to reuse the number so using this form it will find the lowest number that has not been used and reuse it to produce the printer name.