Jump to content

Welcome to Geeks to Go - Register now for FREE
Geeks To Go is a helpful hub, where thousands of friendly volunteers serve up answers and support. 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. This message and all ads will be removed once you have signed in.
Create an Account Login to Account

Auto-filter and line in Excel VBA


  • Please log in to reply

#1
CatherineLavoie

CatherineLavoie

    New Member

  • Member
  • Pip
  • 1 posts
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!

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

  • 0

Advertisement


#2
dm27

dm27

    Member

  • 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?

Edit:
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, _
                            MatchCase:=False)
            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
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

Best of luck with your project.

Sincerely,

DM

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

  • 0



Similar Topics: Auto-filter and line in Excel VBA     x


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

featured