VBA Error Message Run-Time Error 1004

[nuttycow13]

I have written vba script but keep getting this error message:

Run-Time error '1004': Method 'Ranger' of Object'_Worksheet' Failed.

Can anyone tell me what's wrong with this code:


Private Sub UserForm_Initialize()
Dim Site As Range
Dim Location As Range
Dim Printer_Type As Range
Dim ws As Worksheet
Set ws = Worksheets("LookUp USSD")

For Each Site In ws.Range("Site")
With Me.Sitebox
.AddItem Site.Value
End With
Next Site

For Each Location In ws.Range("location")
With Me.Locationbox
.AddItem Location.Value
End With
Next Location

For Each Printer_Type In ws.Range("Printer_Type")
With Me.Printer_Type_box
.AddItem Printer_Type.Value
End With
Next Printer_Type

End Sub

Site, Location and Printer_Type are all combobox's, i think the error is the line i have made bold but could be wrong

if anyone could help me that would be brilliant as its really starting to bug me.

Thank you in advance.

 

[nuttycow13]

thank you. i will do that cos i did mean that.

But still get the run-time error 28 Out of Stack Space on the section

Private Sub Next_Number_Click()

Sheet1.Activate

If Site = "UKCH" Then
Sheet2.Activate
ElseIf Site = "NLEI" Then
Sheet13.Activate
ElseIf Site = "USHW" Then
Sheet10.Activate
ElseIf Site = "SGSG" Then
Sheet11.Activate
End If

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Next_Number = ActiveCell

End Sub

the userform opens and i can get the first combobox fine i enter a figure in there then i click the Next_Number button and get the run-time 28 error so it must be this section that affects it. when i step it i get the bold section fails

Ps. thanks guys for the help so far you have been great.

 

[Firefly2012]

I am struggling to see why that would error - does it still error the same if you alter this bit of code:

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Next_Number = ActiveCell

to this:

Next_Number = Activesheet.Range("B1").End(xlDown).Row +1

Note, I have assumed that you wanted the row of the cell and not its contents again. if I am incorrect here, then you need this line instead:

Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

 

[nuttycow13]

ok what i am doing in this section of the code is finding the next empty cell in B and then moving left one to A and reading the number in there and showing it in the text box.

This finds the last cell in B
Range("B1").End(xlDown).Select

Then this would move it down one cell in B onto the empty cell.
ActiveCell.Offset(1, 0).Select

Then this would move it to the left one to cell A
ActiveCell.Offset(0, -1).Select

This outputs it to textbox Next_Number
Next_Number = ActiveCell

so i will try you code

Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

and see if that works cos it looks like it would.

 

[nuttycow13]

ok the code:

Next_Number = Activesheet.Range("B1").End(xlDown).Offset(1,-1).Value

got a new error message:

Run-time error '-2147417848 (80010108)':

Method 'Range' of object'_Worksheet' failed

 

[Firefly2012]

Can you replace that code with this:

Next_Number = Activesheet.Range("B1").End(xlDown).Row

and report back if this errors or what value is returned to Next_Number if not

 

[nuttycow13]

Sorry Guy i have figured it out it was me again checked out the names and it should have been:


Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Numberbox.Value = ActiveCell

NOT

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Next_Number = ActiveCell

but i have a part of the code that set the format to (0000) so i get 0004 not 4 and this is not working now the code i have is:

Private Sub Next_Number_Click()

Sheet1.Activate

If Site = "UKCH" Then
Sheet2.Activate
ElseIf Site = "NLEI" Then
Sheet13.Activate
ElseIf Site = "USHW" Then
Sheet10.Activate
ElseIf Site = "SGSG" Then
Sheet11.Activate
End If

Range("B1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -1).Select
Numberbox.Value = ActiveCell

End Sub

Private Sub Number_Change()
Numberbox.Text = Format(Number, "0000")
End Sub

I dont know why this would now be affected

 

[nuttycow13]

Ok i have sorted this thanks to both Firefly2012 and p45cal for all your help.