+ Reply to Thread
Results 1 to 7 of 7

No clue where to start - need to move cursor relative to a found cell.

Hybrid View

  1. #1
    Registered User
    Join Date
    05-02-2013
    Location
    Tampabay, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    No clue where to start - need to move cursor relative to a found cell.

    I have a spreadsheet that I use for entering received inventory from packing lists. I search (CTRL+F) enter the item number from the packing list and Excel finds it. The item number is always in column 1. Then I have to cursor over 4 cells to the received column and enter the number received, so that price labels may be printed. Does not seem too hard to do, but when entering a few hundred items, that's a LOT of keystrokes. I'm trying to write a macro that would callup the find function, allow me to enter the item number, find it, move the cursor right 4 cells and put the number 1 in the cell, and remain there in case there are more than one received, which is not often the case.
    I tried using the macro recorder to get an idea of the code, but everything was an absolute value and frankly, not valuable to me. Eventually I would like to add other functionality to it, like checking the number on hand against received and so forth, but really need an idea on how to get started.
    Last edited by ExNavy91; 05-02-2013 at 04:52 PM. Reason: correct spelling

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: No clue where to start - need to move cursor relative to a found cell.

    I setup a key combination to call macro to move cursor to cell of choice.

    In ThisWorkbook module I have:
    Private Sub workbook_open()
    'SHIFT +, CTRL ^, ALT %
    Application.OnKey "^q", "TABOVER"
    Application.OnKey "%s", "TABOVER_SN"
    'Application.OnKey "%r", "StartCol3"
    Application.OnKey "%`", "StartCol3"
    End Sub
    Then in a standard module, I have:
    Sub TabOver()
    'Cells.goto ("Q" & ActiveCell.Row)
    Application.GoTo Reference:=Worksheets("DATA").Range("W" & ActiveCell.Row)
    End Sub
    
    Sub TabOver_SN()
    'Cells.goto ("Q" & ActiveCell.Row)
    Application.GoTo Reference:=Worksheets("DATA").Range("Q" & ActiveCell.Row)
    End Sub
    
    Sub StartCol3()
    If Not Application.CutCopyMode Then
            Worksheets("DATA").Range("C" & ActiveCell.Row + 1).Select
    End If
    End Sub
    Last edited by Tinbendr; 05-02-2013 at 05:14 PM.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    05-02-2013
    Location
    Tampabay, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: No clue where to start - need to move cursor relative to a found cell.

    I appreciate your quick reply, but it is greek to me. I need it to always tab over 4 cells once it has found the cell i am looking up. There seem to be way too many options in your example, I'm sorry I do not understand. Maybe i am not explaining it well enough. Here is an outline of what i am trying to accomplish:
    1. Bring up Find Dialog (normally CTRL+F)
    2. Type in part number to find (ex. NLT317GF)
    3. Excel finds part number in column 1, unknown row and places cursor there (Normal action for find)
    4. Dismiss Find Dialog box (ESC)
    5. Cursor over 4 cells to right (always), enter the number 1 and dismiss the macro

    Would like to use 1 hot key to access this if possible

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: No clue where to start - need to move cursor relative to a found cell.

    See the uploaded example.

    If this will suffice, copy the code over to your workbook.
    Last edited by Tinbendr; 05-02-2013 at 08:11 PM.

  5. #5
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: No clue where to start - need to move cursor relative to a found cell.

    Quote Originally Posted by Tinbendr View Post
    See the uploaded example.

    If this will suffice, copy the code over to your workbook.
    Hi - i have a question on this, your example takes you to column E (being 4 columns over from the products....)

    Any ideas how to take you four columns over from the cell found? i.e. if for example the product was actually in collumn B then going to column E would only be 3 columns across???

  6. #6
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: No clue where to start - need to move cursor relative to a found cell.

    Replace the line
    Application.GoTo Reference:=Worksheets(1).Range("E" & ActiveCell.Row)
    with this.

    Application.GoTo Reference:=ActiveCell.Offset(, 3)

  7. #7
    Registered User
    Join Date
    05-02-2013
    Location
    Tampabay, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: No clue where to start - need to move cursor relative to a found cell.

    AWESOME! The uploaded code works for what I need and provides me with a foundation I can build on. I do not know why I was having a mental block. I could get the cursor to move but not relative to the found cell.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1