Finding a matching values

[morry]

Hello people,

I am having a little trouble.

I am trying to write an if statement into my submit button that will
match the number that is in a text box on my form with a number listed
in column G of a sheet.

On my form there are three text boxes and a submit button. In column G
of my sheet i have a list of part numbers. When i press the submit
button i want it to locate the number in column "G" on the sheet that
matches the number in the text box (DPartNum.Text) and then place an *
in column "O" of the same row that the number was found in column G.

Below is the code that i have tried to create.


Private Sub Submit_DNotes_Click()

With Worksheets("Notes")

Worksheets("Notes").Range("1:500").Rows.AutoFit
cLastRow = .Cells(Rows.Count, "F").End(xlUp).Row
..Cells(cLastRow + 2, "F").Value = "Change Number: " &
ChangeNum.Text
..Cells(cLastRow + 3, "F").Value = "Part Number: " &
DPartNum.Text
..Cells(cLastRow + 4, "F").Value = " " & DNotes_Box.Text

End With
If DPartNum.Text = Worksheets("Disposition").Range("G1:G500") Then
Worksheets("Disposition").Range("O").cols.Insert ("*")
End If

DNotesForm.Hide
ChangeNum.Text = ""
DPartNum.Text = ""
DNotes_Box.Text = ""
End Sub


The With statement is a different function that the button performs.

Thank you
Morry

 

[Dick Kusleika]

Morry

You can either loop through all the cells until you find the one you want,
or use the Find method to directly to it.

To loop:

Dim cell As Range

For Each cell In Sheets("Disposition").Range("G1:G500")
If cell.Value = DPartNum.Text Then
cell.Offset(0,8).Value = "*"
Exit For
End If
Next cell

for Find:

Dim FndCell as Range

Set FndCell =
Sheets("Disposition").Range("G1:G500").Find(What:=DPartNum.Text,
LookAt:=xlWhole)

If Not FndCell Is Nothing Then
FndCell.Offset(0,8).Value = "*"
End If

Find will be faster, but not appreciably so for 500 rows.

 

[morry]

Thank you very much Dick.

Your code worked perfect.
I have been working on this for a couple days now and nothing i trie
was working.

Thank you again
Morr