Jump to content

Create Account How it Works

Auto-filter and line in Excel VBA

  • Please log in to reply



    New Member

  • Member
  • Pip
  • 1 posts

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:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _
Sheets("Sheet2").Range("F2"), Operator:=xlAnd

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!

Edited by CatherineLavoie, 12 October 2011 - 01:31 PM.

  • 0





  • Member
  • PipPipPip
  • 272 posts
Hello CatherineLavoie,

Perhaps you have found the solution to your problem, but if not, would the Match formula work for your issue? If not, is the data in Sheet 2, Column F all unique items or do the values repeat?

I adapted the below code from this site to match your specific criteria. I'm not sure how you are using the row number so I added a message box to display it.

Sub Find_First()
'Source: http://www.rondebruin.nl/find.htm

    Dim FindString As String
    Dim Rng As Range
    'change findString as required
    'FindString = InputBox("Enter a Search value")
    FindString = Sheets("Sheet1").Range("F2").Value
    If Trim(FindString) <> "" Then
        With Sheets("Sheet2").Range("F:F")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
            If Not Rng Is Nothing Then
            'if you don't need to navigate to the actual value, then comment out the line below
                Application.Goto Rng, True
                MsgBox "The active cell row is " & ActiveCell.Row
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

Best of luck with your project.



Edited by dm27, 14 October 2011 - 06:32 PM.

  • 0

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Malware Removal How to Guides Windows 7 System Building Download Files Register welcome

Never used a forum? Learn how.