Run Time Error

[twildone]

Hello everyone,

I have attached an excel workbook entitled the Data. I have a userform and have created command buttons on worksheet titled “Search” that run a macro to search based on different search criteria.
The problem I am having is the first 5 command buttons work fine but the last 4 command buttons don’t function properly. When the command button is clicked and a selection is made from a drop down list, a Run Time Error ‘1004’ message pops up.

Can anyone tell me where the error is? Thank you in advance.

[ajryan88]

At a quick glance, you haven't defined the variable "uRows" anywhere in any of the code. I got the error when I clicked on the first button, and I didn't bother testing anything after that.

Also, you need to change your code from (in every instance):

uRows = Sheets("Master").Range("B100000").End(xlUp).Row

to

uRows = Sheets("Master").Cells(Sheets("Master").Rows.Count, "B").End(xlUp).Row

It might be a bit tidier if you use a

With Sheets("Master")
    
End With

statement also...

[coreytroy]

try attached, to see if you get the same error.

[Richard Buttrey]

Hi,

It looks like it was created in XL 2003 which only has 65536 rows. You have references to row 100,000 before some .End(xlup) instructions so that will fail. Always use code like Range("A" & Rows.Count).End(xlup).

Neither is uRows dimensioned as a Long Variable.

In addition the sheet names you're adding use the Variable X which is not a string variable. Furthermore when this is converted to a string it contain the disallowable character : so this will need to be removed. Hence the line of code you need to use is:

Sheets.Add.Name = Replace(CStr(X), ":", "")

[protonLeah]

I tried debugging your code and got many errors due to missing or mis-placed "End Sub"'s and undefined variables

[twildone]

I have made some of the suggested changes that some of you that suggested. It certainly helped but now I am getting a Run Time Error '9' message and when I click debug the following line is highlighted:

Rows(1).Copy Sheets(X).Range("A" & Rows.Count).End(3)(2)

I have created and use Excel 2007. Thanks once again for taking the time to respond with your much appreciated suggestions.

Private Sub cmdSubmit_Click()

Dim d As String
Dim X As String
Dim i As Long
Dim y As Long
Range("AA1").Value = cboTheClassD
cboTheClassD = Empty
frmTheClassDEntry.Hide

X = cboTheClassDName

Sheets.Add.Name = Replace(CStr(X), ":", " ")

Sheets("Master").Activate

d = Range("AA1").Value

i = 1
    Do Until ActiveCell.Value = d
    Cells(1, i).Select
                
i = i + 1
Loop

Rows(1).Copy Sheets(X).Range("A" & Rows.Count).End(3)(2)
Sheets(X).Range("A1").EntireRow.Delete shift:=xlUp

Sheets("Master").Activate

For y = 2 To 100000
    If Cells(y, ActiveCell.Column) = X Then
        Cells(y, ActiveCell.Column).EntireRow.Copy Sheets(X).Range("A" & Rows.Count).End(3)(2)
    End If
    
Next y

Sheets(X).Activate
Cells.Select
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues
    Do Until Cells(1, 1) <> ""
        Cells(1, 1).Select
        Cells(1, 1).EntireColumn.Delete shift:=xlToRight
   Loop
    i = 1
    For i = 1 To Worksheets.Count
        Worksheets(i).Tab.ColorIndex = i + 10
        
    Next
    End Sub

[Richard Buttrey]

Hi,

Probably because you are still using the Sheets(X) syntax.

Remember we had to change this in the line that added the new sheet since X is not a string variabe. So wherever you have Sheets(X) substitute

Replace(CStr(X), ":", " ")

[twildone]

Hi Richard,

Thank you so much for your input and suggestions. It finally works like I wanted it to work. Actually I had to replace the "X" in Sheets(X) with Replace(CStr(X),":"," ")