How to find a matching row and Insert Data

[hon123456]

Dear all,

I have Excel with two Column, One column is named
Invoice_number and one column is named Checked_Value which will have
default value "" (Blank) . Invoice_number can have several thousand
rows. Now I write a userform1 with a textbox, when the user enter a
Invoice Number in the textbox, the VBA will try to find a matching
value in the the Column Invoice_number. How can I find a matching
value in column Invoice_number with Checked_value equal "". After the
matching invoice number is found , the Checked_Value column will be
changed to "Yes", and this row will not be compared again when the
user enter Invoice number in textbox. How Can I do that?

Thanks

 

[GTVT06]

Dear all,

          I have Excel with two Column, One column is named
Invoice_number and one column is named Checked_Value which will have
default value "" (Blank) . Invoice_number can have several thousand
rows. Now I write a userform1 with a textbox, when the user enter a
Invoice Number in the textbox, the VBA will try to find a matching
value in the the Column Invoice_number. How can I find a matching
value in column Invoice_number with Checked_value equal  "". After the
matching invoice number is found , the Checked_Value column will be
changed to "Yes", and this row will not be compared again when the
user enter Invoice number in textbox. How Can I do that?

Thanks

Dim i As String
Dim cell As Range
i = InputBox("Invoice number?")
If i = "" Then Exit Sub
For Each cell In Range("Invoice_number")
If cell.Value = i Then
cell.Offset(0, 1).Select
Selection.Value = "Yes"
End If
Next cell

 

[hon123456]

Dim i As String
Dim cell As Range
i = InputBox("Invoice number?")
If i = "" Then Exit Sub
For Each cell In Range("Invoice_number")
If cell.Value = i Then
cell.Offset(0, 1).Select
Selection.Value = "Yes"
End If
Next cell

Thanks GTVT06, but I want VBA to Search the Invoice_Number with
Checked_Value = "". Your code seems to search every cell but not the
cell with
Checked_value = "". What I want is the Checked_Value is change to
"Yes, It will not be Search again.

Thanks.

 

[hon123456]

Thanks GTVT06, but I want VBA to Search the Invoice_Number with
Checked_Value = "". Your code seems to search every cell but not the
cell with
Checked_value = "". What I want is the Checked_Value is change to
"Yes, It will not be Search again.

Thanks.- $Bp,i6Ho0zMQJ8;z(B -

- $Bp}<(Ho0zMQJ8;z(B -

Dear GTVT06,

When the VBA goto the following statement:

For Each cell In Range("Invoice_number")

Error 1004 was generated, the error message is " Range Method (_Global
Object) Failed" . I have type the "Invoice_number" in Cell A1 but it
does not work. Please help. Thanks.

 

[GTVT06]

Dear GTVT06,

When the VBA goto the following statement:

For Each cell In Range("Invoice_number")

Error 1004 was generated, the error message is " Range Method (_Global
Object) Failed" . I have type the "Invoice_number" in Cell A1 but it
does not work. Please help. Thanks.- Hide quoted text -

- Show quoted text -

hon123456,
see if this is more like what you were looking for. I also forgot you
were using forms and not inbutboxes, I wrote this using a inputbox but
it can easily be converted to your form. If you need help converting
it over, let me know.

Dim i As String
Dim cell As Range
i = InputBox("Invoice number?")
If i = "" Then Exit Sub
For Each cell In Range("A:A")
If cell.Value = i Then
cell.Offset(0, 1).Select
If Selection.Value = "" Then
Selection.Value = "Yes"
End If
End If
Next cell