How can I do the following: If a

[Navvy]

How can I do the following:

If a user enters an age via a userform. I want to search through two columns, D & E. for that value. The best way is to run through both the list and if it equals that value, put a "YES" or "NO" in column F and then autofilter that column.

So, first it will go through D, put YES or NO, then go through E and put YES or NO.

I don't know how to do this. Can anyone help?

 

[waynerenaud]

Hi Navvy, I am not a programmer or developer but you can try the following as a start. I assume you have a textbox on your userform, for this example I will assume it is called textbox1.
Add a button to your userform and put the following in the click event.

IMPORTANT - Note that this will remove all values in column F so try on sample data first.

Private Sub CommandButton1_Click()
Dim lRow As Long
Dim CountMe
CountMe = 0
lRow = [d65536].End(xlUp).Row
Range("F:F&quot.Value = ""
For i = 1 To lRow
If Cells(i, 4).Value = UserForm1.TextBox1.Text Then
Cells(i, 4).Offset(0, 2).Value = "Yes"
CountMe = CountMe + 1
End If
Next i
lRow = [e65536].End(xlUp).Row
For i = 1 To lRow
If Cells(i, 5).Value = UserForm1.TextBox1.Text Then
Cells(i, 5).Offset(0, 1).Value = "Yes"
CountMe = CountMe + 1
End If
Next i
Range("F:F&quot.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Yes"
MsgBox "There were " & CountMe & " instances of " & UserForm1.TextBox1.Text & " Found"
End Sub

 

[waynerenaud]

Hi Navvy, Slight change to ensure the autofilter is turned off before staring the search. On the line Set w = Worksheets("Sheet1&quot replace sheet1 with the name of your sheet. Hope it helps


Private Sub CommandButton1_Click()
Dim lRow As Long
Dim CountMe As Integer
Dim w As Worksheet
CountMe = 0
lRow = [d65536].End(xlUp).Row
Range("F:F&quot.Value = ""
Set w = Worksheets("Sheet1&quot
w.AutoFilterMode = False
For i = 1 To lRow
If Cells(i, 4).Value = UserForm1.TextBox1.Text Then
Cells(i, 4).Offset(0, 2).Value = "Yes"
CountMe = CountMe + 1
End If
Next i
lRow = [e65536].End(xlUp).Row
For i = 1 To lRow
If Cells(i, 5).Value = UserForm1.TextBox1.Text Then
Cells(i, 5).Offset(0, 1).Value = "Yes"
CountMe = CountMe + 1
End If
Next i
If CountMe = 0 Then
MsgBox "None Found"
Exit Sub
End If
Range("F:F&quot.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Yes"
MsgBox "There were " & CountMe & " instances of " & UserForm1.TextBox1.Text & " Found"
End Sub

 

[waynerenaud]

Slight change to code to prevent errors if filtering after you have already done 1 filter. Change the order of the following lines as below........(moved lrow below w.autofiltermode)

Set w = Worksheets("Sheet1&quot
w.AutoFilterMode = False
lRow = [d65536].End(xlUp).Row