Match, Search and GoTo cell in another sheet Marco.


Need someHELP and assistance in creating a Macro for a button in Excel 2013.

I have a workbook consisting of the following:

Sheet 1, is for search assets, 'SE'.

Sheet 2, 'Data" is all the relevant information on assets. What I need to do is a macro for a button.

So, when I enter a barcode in the cell on sheet.1, 'SE', and I get a match, once I press the button it will take me to the line 'Seq No' on sheet.2 so I can update the data.

Mostly, all of the required cells are working well, where I do need help is creating a macro for the button to: 'Search and GOTO' from Sheet.1 to Sheet.2.

I will upload a sample of the workbook.



Selected Answer

This should do the job for you.

Sub GoToData()
    ' 29 Mar 2018
    Const FirstRow As Long = 3
    Dim Target As Variant
    Dim Rng As Range
    Dim R As Long
    Target = Worksheets("SE").Cells(5, 3).Value         ' = C5
    With Worksheets("Data")
        R = .Cells(.Rows.Count, 1).End(xlUp).Row        ' look in column 1
        Set Rng = Range(.Cells(FirstRow, 1), .Cells(R, 1))
        On Error Resume Next
        R = Application.Match(Target, Rng, 0)
        If Err Then
            MsgBox "The item """ & Target & """ doesn't exist.", _
                   vbInformation, "No match found"
            R = R + FirstRow - 1
            Range(.Cells(R, 1), Cells(R, 10)).Select
        End If
    End With
End Sub


I thank you kindly; done some checks so far the code is working fine. I will try and learn from your code. Thanks again 
jdgrapes (rep: 2) Mar 29, '18 at 5:15 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login