Filter using a cell value as the criteria

[colinharwood]

Hi
I would like to use the value of range X2 on sheet 3 , (which is a number), as the criteria when using filter on col 1 sheet 1 ( the contents of col 1 are all numbers).
This will return 1 row and I would then like to put todays date in col Q of the row that is filtered.
Is this possible please.
Thanks

 

[dermie_72]

1. Select a cell where you want to display the filtered data.
2. Write the FILTER function in that cell.
3. The first argument of the function is the range of data you want to filter.
4. The second argument is the filter criteria. Sheet 3 X2.
5. The third argument is optional. It specifies what to display if no data is found.

 

[Skyybot]

Try this

Option Explicit

Sub EnterDateonFilteredRow()
Dim wb As Workbook, sht1 As Worksheet, sht3 As Worksheet, filter_value As Range, filter_range As Range
Dim cell As Range, found_row As Long
Set wb = ThisWorkbook
Set sht1 = wb.Sheets("Sheet1") ' Change Sheet1 to your sheet number 1 name
Set sht3 = wb.Sheets("Sheet3") ' Change Sheet3 to your sheet number 3 name
Set filter_value = sht3.Range("X2")
Set filter_range = sht1.Range("A1:A" & sht1.Columns(1).Rows.End(xlDown).Row)
filter_range.AutoFilter Field:=1, Criteria1:=filter_value.Value
For Each cell In filter_range.Offset(1)
    If cell.Height <> 0 Then
        sht1.Range("Q" & cell.Row).Value = Date
        filter_range.AutoFilter
        Exit Sub
    End If
Next cell
End Sub

 

[Peter_SSs]

See (& possibly adapt as indicated) my suggestion in your other (now closed) thread here.

 

[colinharwood]

Peter
Thank your for your reply, which is exactly what I was trying to do.
Sorry for my misdemeanours, your comments are duly noted.

 

[Peter_SSs]

Cheers. Glad it worked for you. Thanks for the confirmation.