Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Match, Search and GoTo cell in another sheet Marco.

0

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.

excel
posted by: jdgrapes (rep: 14) on Wed Mar 28, 2018 at 9:23 pm
Answer
Discuss

Answers

0
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"
        Else
            R = R + FirstRow - 1
            .Activate
            Range(.Cells(R, 1), Cells(R, 10)).Select
        End If
    End With
End Sub
posted by: Variatus (rep: 4889) on Thu Mar 29, 2018 at 6:11 am
Discuss

Discussion

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: 14) 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