Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

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

Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP