PDA

View Full Version : Want to get numbers to repeat in a spreadsheet like words do.


eedie
Apr 25, 2009, 01:52 PM
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.

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

JBeaucaire
Apr 25, 2009, 02:22 PM
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:
Highlight the entire column and format it for TEXT (Ctrl-1, Number: Category: Text)
Now go down your existing data and lets convert IT to text:
In an adjacent empty column enter this formula:
=A1&""
Change the A1 to whatever the first cell reference is. This will convert the value there into a TEXT representation of the same string.
Copy that cell down the whole column...as far as the data set goes
Now highlight the entire new column of value you've created and press CTRL-C to copy it
Click on the top cell in the original values and click on EDIT > PASTE SPECIAL > Values
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.

JBeaucaire
Apr 25, 2009, 02:30 PM
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.

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.