Auto-filter and line in Excel VBA

[CatherineLavoie]

Hi!

I've been trying to create a macro for a specif need for 3 days, and can't find how...
Hope someone could help me here!


I would like to find and select the row in the column F of Sheet2 (called "Table1" in the macro) that contains the value of the cell F2 of the Sheet1.
Since there's a lot of data in the the column F of Sheet2, I'm using the auto-filter.

Here what I've done:

Range("F2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B2").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _
Sheets("Sheet2").Range("F2"), Operator:=xlAnd
ActiveCell.Select


The macro does find the cell. But it did not select the cell - which is the most important because I need the row number!

Could someone help me on this?

Thanks so much!

[JBeaucaire]

Hi Catherine, welcome to the forum.

1) Please edit your post above and add code tags around your posted code. My signature shows an example of the tags you need to add before and after the code.

All my code goes in here

2) Don't select sheets/cells etc with VBA. It's not necessary.

Dim MyRow as Long

With Sheets("Sheet1").ListObjects("Table")
    .ListObjects("Table").Range.AutoFilter Field:=5, Criteria1:=Sheets("Sheet2").Range("F2")
    MyRow = .Columns(5).Find(Sheets("Sheet2").Range("F2"), LookIn:=xlValues, LookAt:=xlWhole).Row
End With

MsgBox "The row is " & MyRow

Once you fix the post above, consider posting up a sample workbook showing us BEFORE/AFTER what you're really trying to accomplish. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.