Get data from another sheet

[efernandes67]

Hi,

I am in sheet("game1") and I want to get some info sheet("game2") unsing the match function.

I have the following code that works just fine. but!!!

Sub Test1()
    Dim rw as long 

    Application.ScreenUpdating = False
    Sheets("game2").Select
   
    With Application.WorksheetFunction
        rw = .Match(, Range("G:G"), 0)
    End With

    Sheets("game1").Select
    Application.ScreenUpdating = True
end sub

I want to make a change so that I dont have to swap between sheets.
I tried the following code, but I am missing something!

Sub Test2()
    Dim rw as long 
    Dim wsData As Worksheet

    'Set wsData = Worksheets("game2")

    With Application.WorksheetFunction
        rw = .Match(, Range("G:G"), 0)
    End With
end sub

Regards,
Elio Fernandes

[TMS]

I'm struggling toget your code to compile, let alone run.

Please post the workbook with the working code.

Regards

[efernandes67]

Here is the file that you asked for.
I want to use procedure "Test2" after you correcting the error in it.

Regards,
Elio Fernandes

[shg]

efernandes67, please don't quote whole posts; it's just clutter.

Use the REPLY button, not the QUOTE button.

Thanks.

[TMS]

OK:

Sub GetRow_G2()

    With Sheets("game2")
        Range("G7").Value = "NF"
        On Error Resume Next
        Range("G7").Value = _
            Application.WorksheetFunction.Match _
                (Sheets("game1").Range("E7"), .Range("G:G"), 0)
        On Error GoTo 0
    End With

End Sub

You could combine this with a Worksheet Change event (in sheet game1) so that the field will update automatically:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("E7")) Is Nothing Then Exit Sub
Application.EnableEvents = False
GetRow_G2
Application.EnableEvents = True

End Sub

And if you added Data Validation on the name input field (using the list of names on sheet game2) you could ensure that you don't mis-enter any names.

Regards

[efernandes67]

Thanks, it's just fine.

Regards
Elio Fernandes