VBA code to find the value of cells

[Akw47]

Hi currently I have this code

Private Sub TextBox1_Change()
    Dim c As Range
    If Len(TextBox1.Value) >= 5 Then
            If c in ThisWorkbook.ActiveSheet.Range("D1", .Cells(.rows.Count, "D").End(x1Up)) Then
             If InStr(c, "/") > 0 Or InStr(c, ",") > 0 Then
                    If MsgBox("Please confirm cell " & c.Address, vbYesNo, "Double Check") = vbNo Then Exit Sub
            End If
        End If
    End If
End Sub

But there is a error at the "if c in thisworkbook" statement. I want MsgBox to pop out when my the 5 digit number in textbox1 match with the same number in column D with a "/" or "," with it

 

[eduzs]

Firstly, you didn't defined what is "c" range, it's null.

 

[Akw47]

Firstly, you didn't defined what is "c" range, it's null.

yea i want the range to be

Range("D1", .Cells(.rows.Count, "D").End(x1Up))

but not sure how to phrase it

 

[Joe4]

You have a bunch of mistakes in your code (using . reference without a "With" statement, typing "x1Up" instead of "xlUp"), and it is hard to figure out exactly what you want to happen by the way you have attempted to write your code.

Can you just provide a sample of the data, along with an explanation (in plain English) of exactly what you would like to happen?

 

[Akw47]

You have a bunch of mistakes in your code (using . reference without a "With" statement, typing "x1Up" instead of "xlUp"), and it is hard to figure out exactly what you want to happen by the way you have attempted to write your code.

Can you just provide a sample of the data, along with an explanation (in plain English) of exactly what you would like to happen?

So everything is 5 digits long. Whenever, there is a "/" or "," in the cell. I need a MsgBox to pop out, after typing the 5 digit number in my textbox, letting me know which cell is located.

 

[Akw47]

E.G i type 90983 in textbox1, a msgbox pops out to tell me to check D5 again.

 

[Joe4]

So, if all you are doing is searching for the entry in the text box within your list?
You should be able to use FIND to do that, choosing the partial match option.

Can the number appear more than once in the list?
If so, do you want a message box for each one (or one big message box at the end)?

 

[Akw47]

So, if all you are doing is searching for the entry in the text box within your list?
You should be able to use FIND to do that, choosing the partial match option.

Can the number appear more than once in the list?
If so, do you want a message box for each one (or one big message box at the end)?

wait so my code InStr(c, "/") > 0 is not possible?
My number should appear once only. A message box for each one that had been type. EG 90983 is typed, then one will pop out for it.

 

[Joe4]

OK, I am still not quite clear what you are after. So let's look at some examples.

Example 1:
You are looking for value "90983" and the value "90983 / 67893" appears in cell D5.
So, you want your message box to return "Please confirm cell D5", right?

Example 2:
You are looking for value "90983" and the value "90983" appears (all by itself) in cell D7.
Do you want to return anything in this case?

Is it that you only want to return the message box when that value exists in a cell whose entry is more than 5 characters long?

 

[Akw47]

OK, I am still not quite clear what you are after. So let's look at some examples.

Example 1:
You are looking for value "90983" and the value "90983 / 67893" appears in cell D5.
So, you want your message box to return "Please confirm cell D5", right?

Example 2:
You are looking for value "90983" and the value "90983" appears (all by itself) in cell D7.
Do you want to return anything in this case?

Is it that you only want to return the message box when that value exists in a cell whose entry is more than 5 characters long?

Example 1 is what i am looking for. looking for a message box when that value in a cell containt "/" or ","