[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!

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

[Advertisements]

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.

[dm27]

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.