Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Want to get numbers to repeat in a spreadsheet like words do. (https://www.askmehelpdesk.com/showthread.php?t=345981)

  • Apr 25, 2009, 01:52 PM
    eedie
    Want to get numbers to repeat in a spreadsheet like words do.
    If I type a word in a cell on the spreadsheet, and further down the column if I type the beginning letter of that word, it will repeat. However, that only works with words. I cannot find a way to get a number to repeat in that same fashing. I have two columns where a certain number is used almost 100% of the time. Is there a way that I can get the number to repeat the same way that words do.
  • Apr 25, 2009, 02:18 PM
    ScottGem

    No the autocomplete works only with text. However you can create a macro that can type in the number with simple keystroke combination.
  • Apr 25, 2009, 02:22 PM
    JBeaucaire

    No, there is no builtin autocomplete for numbers. But... you can cheat. Unfortunately it will require some editing of the existing sheet on your part.

    Since autocomplete only works for text, you need to:
    1. Highlight the entire column and format it for TEXT (Ctrl-1, Number: Category: Text)
    2. Now go down your existing data and lets convert IT to text:
      1. In an adjacent empty column enter this formula:
        =A1&""
      2. Change the A1 to whatever the first cell reference is. This will convert the value there into a TEXT representation of the same string.
      3. Copy that cell down the whole column...as far as the data set goes
      4. Now highlight the entire new column of value you've created and press CTRL-C to copy it
      5. Click on the top cell in the original values and click on EDIT > PASTE SPECIAL > Values
      6. Now delete the column you created

    Ok, now that you've converted all the existing data in this column to TEXT, and formatted the column for TEXT, autocomplete will SORT OF work for you, but not automatically.

    Numeric-looking text strings STILL won't pop up on their own like "cat" or "dog" would, but in the next empty cell down you can press ALT-DownArrow and a picklist will appear that includes your numeric-looking text strings.

    That's about the best you can do...
    =========

    A semi-lame idea is to just enter ZZ for that number during a bunch of data entry and then use one SEARCH/REPLACE on that column to convert all the ZZ entries to your special number.

    =========
    Ooh... that makes me think, you could create a Worksheet_Change event that watches that column and anytime it sees you enter ZZ, it changes it to your common number. That would be cool, but you'd have to have macros active all the time.

    If the other ideas above don't suffice and you want to try an active macro, let me know. I can help with that if you don't know how to create the macro.
  • Apr 25, 2009, 02:30 PM
    JBeaucaire

    Scott's idea is the best. Turn on the macro recorder being sure to assign a keyboard-shortcut as you start it, let it record you typing in the number. Then stop the recorder.

    Now click ALT-F8, click on your new macro and select EDIT.

    Inside the macro you'll see two-line... the one where you entered your code into the active cell and the next cell your cursor selected. Delete that second line.
    Code:

        ActiveCell.FormulaR1C1 = "1234"
        Range("C9").Select  <<<delete this line



    or change that line above to:
        ActiveCell.Offset(0, 1).Select    'this moves one cell to the right

    Then Alt-Q to close the editor. Save your sheet.

    Now anytime you press your key-combination it will enter your special value.

  • All times are GMT -7. The time now is 12:53 AM.