Forum Discussion

JonHowarth's avatar
JonHowarth
Copper Contributor
Oct 07, 2022
Solved

Create button to jump to a cell

I have a sheet that tracks purchase order numbers in a table. I'd like to add a search function at the top of the spreadsheet, where you enter the PO number in a cell then press a search button and it takes you to the row for that PO number.

 

Alternatively, I've used the match function to find the cell reference of the PO number in the search cell. Could a button then be programmed to scroll to that cell reference?

 

Thanks,

  • JonHowarth 

    In the attached file you can enter the PO number in cell B1 and click the button in cell C1 to activate the cell with the PO number from B1. The macro currently searches for PO numbers in range A4:A1000 which can be adapted.

    Sub activate_po_number()
    
    Dim i As Long
    
    On Error GoTo errormessage
    
    If Not IsError(i = WorksheetFunction.Match(Range("B1").Value, Range("A4:A1000"), 0)) Then
    
    i = WorksheetFunction.Match(Range("B1").Value, Range("A4:A1000"), 0)
    
    Cells(i + 3, 1).activate
    
    Else
    
    errormessage:
    MsgBox ("PO number not found")
    
    End If
    
    End Sub

2 Replies

  • JonHowarth 

    In the attached file you can enter the PO number in cell B1 and click the button in cell C1 to activate the cell with the PO number from B1. The macro currently searches for PO numbers in range A4:A1000 which can be adapted.

    Sub activate_po_number()
    
    Dim i As Long
    
    On Error GoTo errormessage
    
    If Not IsError(i = WorksheetFunction.Match(Range("B1").Value, Range("A4:A1000"), 0)) Then
    
    i = WorksheetFunction.Match(Range("B1").Value, Range("A4:A1000"), 0)
    
    Cells(i + 3, 1).activate
    
    Else
    
    errormessage:
    MsgBox ("PO number not found")
    
    End If
    
    End Sub
    • JonHowarth's avatar
      JonHowarth
      Copper Contributor
      That works perfectly! Do you know how I can make it so the cell it scrolls to is the top row? At the moment, it scrolls to the cell in the middle of the page.

Resources