Trying to figure out how to code a GOTO macro

**BKarney** · 08-16-2014, 07:32 PM

I'm a VBA novice, and have taken on a project that's currently beyond my skills, though I'm learning as fast as I can.

My user has a small database in Excel (700 rows, 30 columns). The order # field (Column D) is a 5-digit number (there are no alpha characters). There is also a single-digit line-item # (Column E). The DB is sorted, with the primary key being order # and the secondary key being line-item #.

The user wants a macro that will allow him to enter an order # and be taken to the first row in the database that matches that order #. He does not want to do this by using Excel's "Find" command -- he wants a macro.

For example, if the order # he wants to go to is 13002, he would be satisfied with either:
* Invoke the macro, then enter 13002 or
* Enter 13002 in a specific cell in the worksheet and then invoke the macro

In either case, the desired result is to activate the cell in Column D of the first row of the database where Order # = 13002.

**mikerickson** · 08-17-2014, 12:05 AM

Would this work for you?

Sub FindSomething()
    Dim uiValue As Variant
    
    uiValue = Application.InputBox("Enter an order #", Default:="13002", Type:=7)
    If uiValue = False And TypeName(uiValue) = "Boolean" Then Exit Sub: Rem cancel pressed
    
    On Error Resume Next

    With ActiveSheet.UsedRange
        Application.Goto .Find(What:=uiValue, After:=.Cells(.Rows.Count, .Columns.Count), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
    End With

    If Err Then Beep
    On Error GoTo 0
End Sub

**BKarney** · 08-17-2014, 07:08 PM

Thank you, Mike. This works, and I really appreciate you taking the time to help me.

Trying to figure out how to code a GOTO macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Trying to figure out how to code a GOTO macro

Re: Trying to figure out how to code a GOTO macro

Re: Trying to figure out how to code a GOTO macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Skip this Macro Goto another - code required

[SOLVED] How to insert sub-caption for figures? figure 1, figure 2-a, figure 2-b, figure 3, ...

GoTo code

GoTo or Run Code???

[SOLVED] Goto code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions