Mistake in Code??

[charllie]

Hi Folks,

Could someone tell me where i am going wrong please with the following code.

Private Sub TextBox4_Change()
    If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6") = "HANDOVER" Then
        If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
            Dim ctl As MSForms.Control
            Dim i As Integer
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
                    Private Sub TextBox4_Change()
    If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6") = "HANDOVER" Then
        If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
            Dim ctl As MSForms.Control
            Dim i As Integer
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
                    For i = 5 To 200
                        If Me.TextBox4.Text = .Cells(i, "C") Then
                            Exit Sub
                        Else
                            MsgBox "HELP"
                            Exit Sub
                        End If
                    Next
                End With
        End If
    End If
End Sub

With this part of the code:

If Me.TextBox4.Text = .Cells(i, "C") Then
                            Exit Sub
                        Else
                            MsgBox "HELP"
                            Exit Sub
                        End If

I am trying to say TextBox4.text is the same as what is in rows C then do nothing.
But if it is does not match what is in row c then show msgbox"HELP".


The error i am getting is no matter what i put in the textbox4 the msgbox"HELP" always shows.

Thanks

 

[al_b_cnu]

Hi Erik,

I tend to prefer MATCH rather than FIND, mainly because I know Match but not FIND - didnt I read somewhere that FIND will return true for a partial match?

Alan

 

[erik.van.geit]

I tend to prefer MATCH rather than FIND

this will depend of charllies answers

FIND will return true for a partial match?

FIND can lookat xlWhole or xlPart
in the helpfiles you can find the complete syntax and a proceduresample

 

[al_b_cnu]

Thanks Erik.

Alan

 

[charllie]

Hi Erik/Alan,

So what is the best way to go round this then so it will look at the whole column. Please.

Thanks

 

[Tazguy37]

Perhaps use a different loop construction, a Do While, for example:

Dim found As Boolean
Dim i As Long

    found = False
    i = 5
    Do
        If Me.TextBox4.Text = Cells(i, "C") Then found = True
        i = i + 1
    Loop While i <= 200 And Not found

HTH

 

[charllie]

Hi Taz,

Thanks for the advice. However i still can't get it to work. Here is the modified code i am using, where am i going wrong please?

If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6") = "HANDOVER" Then
        If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
            Dim found As Boolean
              Dim i As Long
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
                    found = False
                    i = 5
                    Do
                        If Me.TextBox4.Text = Cells(i, "C") Then found = True
                            i = i + 1
                            Loop While i <= 200 And Not found
                        Else
                            MsgBox "You Have Entered The Wrong Order Number, Please Try Again"
                            Me.TextBox4 = ClearContents
                            Exit Sub
                        End If
                    Next
                End With
        End If
    End If

Thanks

 

[charllie]

Hi Folks,

I am really struggling with this one, not sure what i am doing and cant get the code to work.

Can anyone out there help me please.

Thanks

 

[al_b_cnu]

Hi mike,

Not looked at previous posts on his, but isnt this (untested code) what you want:

If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6").Text = "HANDOVER" Then
    If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
        Dim Found As Boolean
        Dim i As Long
        With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
            Found = False
            For i = 5 To 200
                If Me.TextBox4.Text = Cells(i, "C").Text Then
                    Found = True
                    Exit For
                Else
                    MsgBox "You Have Entered The Wrong Order Number, Please Try Again"
                    Me.TextBox4 = ClearContents
                    Exit Sub
                End If
            Next i
        End With
    End If
End If

 

[charllie]

Thanks Alan,

Will give it a try and let you know.

I apprecaite your help.

Mike

 

[erik.van.geit]

Oh, this is a confusing thread
FIND, MATCH, DO WHILE, FOR EACH, FOR NEXT
don't loop when you can do it usinginbuilt functions for the purpose

charllie,
you want to find Me.TextBox4.Text in column C
that's the only thing which is really clear to me
I'm sure this shouldn't be a complicated issue

my suggestions is to post some sample data and what you expect the macro to do
sorry if I'm wrong and missing something, but this seems to me the best solution

kind regards,
Erik