Userform code help with runtime error please

[joespontiac]

Hello, firstly i am really new to using user forms and code and the one i have put together was by trawling the internet and figuring parts out.

So as an overview I have a database and a user form with a "search box". This is looking to find a match in column A but the range starts on row 3 through to row 5263 (this will get larger over time).
The rest of the code is for check boxes (putting 1 or 0 in the specified cells or date pickers upon clicking a "save" button to run the code).
It seem to work well but I have two (possibly related?) errors. Firstly, randomly i get a runtime error 1004 message that when i go to debug it highlights the line "If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then". I htink this has to do with it going through to a cell out of range or something?
Secondly, sometimes I end up with data on a row much further down the sheet rather than on the correct row?

Any help would be appreciated

Option Compare Text

Private Sub CommandButton1_Click()


row_number = 2
Do
DoEvents
row_number = row_number + 1
item_in_review = TextBox1.Text
    If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then

Serial.Text = Sheets("SUBS WIP").Range("D" & row_number)
BATCH.Text = Sheets("SUBS WIP").Range("E" & row_number)
GEN.Text = Sheets("SUBS WIP").Range("F" & row_number)
CAT.Text = Sheets("SUBS WIP").Range("G" & row_number)
STAGE.Text = Sheets("SUBS WIP").Range("BA" & row_number)
MSDate.Value = Sheets("SUBS WIP").Range("AP" & row_number)
RSDate.Value = Sheets("SUBS WIP").Range("AR" & row_number)
ASDate.Value = Sheets("SUBS WIP").Range("AT" & row_number)
FDate.Value = Sheets("SUBS WIP").Range("AK" & row_number)
SDate.Value = Sheets("SUBS WIP").Range("AL" & row_number)
Scrapped.Text = Sheets("SUBS WIP").Range("AM" & row_number)
SAPClosed.Text = Sheets("SUBS WIP").Range("AO" & row_number)
MRDate.Text = Sheets("SUBS WIP").Range("AQ" & row_number)
WRDate.Text = Sheets("SUBS WIP").Range("AS" & row_number)
ARDate.Text = Sheets("SUBS WIP").Range("AU" & row_number)
TextBox3.Text = Sheets("SUBS WIP").Range("BK" & row_number)

    
        Survey.Value = Sheets("SUBS WIP").Range("L" & row_number)
        Decan.Value = Sheets("SUBS WIP").Range("M" & row_number)
        Strip.Value = Sheets("SUBS WIP").Range("I" & row_number)
        MicroSent.Value = Sheets("SUBS WIP").Range("J" & row_number)
        MicroRet.Value = Sheets("SUBS WIP").Range("K" & row_number)
        Rewind.Value = Sheets("SUBS WIP").Range("N" & row_number)
        Grind.Value = Sheets("SUBS WIP").Range("O" & row_number)
        SMIss.Value = Sheets("SUBS WIP").Range("P" & row_number)
        SMRet.Value = Sheets("SUBS WIP").Range("Q" & row_number)
        ShortBuild.Value = Sheets("SUBS WIP").Range("R" & row_number)
        WeldSent.Value = Sheets("SUBS WIP").Range("S" & row_number)
        WeldRet.Value = Sheets("SUBS WIP").Range("T" & row_number)
        LMIss.Value = Sheets("SUBS WIP").Range("U" & row_number)
        LMRet.Value = Sheets("SUBS WIP").Range("V" & row_number)
            Survey2.Value = Sheets("SUBS WIP").Range("W" & row_number)
            Kitted.Value = Sheets("SUBS WIP").Range("X" & row_number)
            AEMSent.Value = Sheets("SUBS WIP").Range("Y" & row_number)
            AEMRet.Value = Sheets("SUBS WIP").Range("Z" & row_number)
                Survey3.Value = Sheets("SUBS WIP").Range("AA" & row_number)
                Strip2.Value = Sheets("SUBS WIP").Range("AB" & row_number)
                Overhaul.Value = Sheets("SUBS WIP").Range("AC" & row_number)
                BalSpin.Value = Sheets("SUBS WIP").Range("AD" & row_number)
                    Survey4.Value = Sheets("SUBS WIP").Range("AE" & row_number)
                    Strip3.Value = Sheets("SUBS WIP").Range("AF" & row_number)
                    Rewind2.Value = Sheets("SUBS WIP").Range("AG" & row_number)
                    Build.Value = Sheets("SUBS WIP").Range("AH" & row_number)
                        Finaled.Value = Sheets("SUBS WIP").Range("AI" & row_number)
                        Scrap.Value = Sheets("SUBS WIP").Range("AJ" & row_number)
                        ISSUE.Value = Sheets("SUBS WIP").Range("H" & row_number)
                        THIRDPARTY.Value = Sheets("SUBS WIP").Range("BJ" & row_number)

        
    End If
    Loop Until item_in_review = ""
    

End Sub





Private Sub CommandButton2_Click()

MsgBox "Well Done Team!"

row_number = 2
Do
DoEvents
row_number = row_number + 1
item_in_review = TextBox1.Text
    If item_in_review = Sheets("SUBS WIP").Range("A" & row_number) Then

        Sheets("SUBS WIP").Range("L" & row_number) = -Survey.Value
        Sheets("SUBS WIP").Range("M" & row_number) = -Decan.Value
        Sheets("SUBS WIP").Range("I" & row_number) = -Strip.Value
        Sheets("SUBS WIP").Range("J" & row_number) = -MicroSent.Value
        Sheets("SUBS WIP").Range("K" & row_number) = -MicroRet.Value
        Sheets("SUBS WIP").Range("N" & row_number) = -Rewind.Value
        Sheets("SUBS WIP").Range("O" & row_number) = -Grind.Value
        Sheets("SUBS WIP").Range("P" & row_number) = -SMIss.Value
        Sheets("SUBS WIP").Range("Q" & row_number) = -SMRet.Value
        Sheets("SUBS WIP").Range("R" & row_number) = -ShortBuild.Value
        Sheets("SUBS WIP").Range("S" & row_number) = -WeldSent.Value
        Sheets("SUBS WIP").Range("T" & row_number) = -WeldRet.Value
        Sheets("SUBS WIP").Range("U" & row_number) = -LMIss.Value
        Sheets("SUBS WIP").Range("V" & row_number) = -LMRet.Value
            Sheets("SUBS WIP").Range("W" & row_number) = -Survey2.Value
            Sheets("SUBS WIP").Range("X" & row_number) = -Kitted.Value
            Sheets("SUBS WIP").Range("Y" & row_number) = -AEMSent.Value
            Sheets("SUBS WIP").Range("Z" & row_number) = -AEMRet.Value
                Sheets("SUBS WIP").Range("AA" & row_number) = -Survey3.Value
                Sheets("SUBS WIP").Range("AB" & row_number) = -Strip2.Value
                Sheets("SUBS WIP").Range("AC" & row_number) = -Overhaul.Value
                Sheets("SUBS WIP").Range("AD" & row_number) = -BalSpin.Value
                    Sheets("SUBS WIP").Range("AE" & row_number) = -Survey4.Value
                    Sheets("SUBS WIP").Range("AF" & row_number) = -Strip3.Value
                    Sheets("SUBS WIP").Range("AG" & row_number) = -Rewind2.Value
                    Sheets("SUBS WIP").Range("AH" & row_number) = -Build.Value
                        Sheets("SUBS WIP").Range("AI" & row_number) = -Finaled.Value
                        Sheets("SUBS WIP").Range("AJ" & row_number) = -Scrap.Value
                        Sheets("SUBS WIP").Range("H" & row_number) = -ISSUE.Value
                        Sheets("SUBS WIP").Range("BJ" & row_number) = -THIRDPARTY.Value
                        Sheets("SUBS WIP").Range("BK" & row_number) = TextBox3.Value
                        
                        
Sheets("SUBS WIP").Range("AP" & row_number) = MSDate.Value
Sheets("SUBS WIP").Range("AR" & row_number) = RSDate.Value
Sheets("SUBS WIP").Range("AT" & row_number) = ASDate.Value
Sheets("SUBS WIP").Range("AK" & row_number) = FDate.Value
Sheets("SUBS WIP").Range("AL" & row_number) = SDate.Value


For Each ctrl In Me.Controls

       Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
             Case "CheckBox"
                ctrl.Value = False
                

        End Select

Next

TextBox1.SetFocus

    End If
    
    
Loop Until item_in_review = ""



End Sub

Private Sub CommandButton4_Click()


For Each ctrl In Me.Controls

       Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
             Case "CheckBox"
                ctrl.Value = False
                

        End Select

Next

TextBox1.SetFocus


End Sub

 

[joespontiac]

I should also mention that this is a shared file if that makes any difference at all

 

[joespontiac]

Does anyone have an idea?

 

[joespontiac]

BUMP