Speed-Up Data Entry by having Excel Complete Strings

  • Thread starter Thread starter 6afraidbecause789
  • Start date Start date
6

6afraidbecause789

HI - How can Excel auto complete a string if a user only types the
first digit and then presses the Enter or arrow keys? For example, a
3-digit string of ‘444’ should be entered if a user enters a ‘4’ and
presses Enter. Likewise...if a user enters 1 and presses Enter, the
result should be 111; 2 + Enter = 222, and 3 + Enter = 333.

NOTE that if a user enters some other full, 3-digit string, like ‘421’
or '123,' the cell MUST retain the 421 or 123. Also note that this
will be a text field. THANK YOU!
 
I'd use two cells--one for the data input and one to return the modified
version.

Say column A contains the data input, then I'd use this in column B:

In B1:
=if(a1="","",if(len(a1)=1,rept(a1,3),a1))
and drag down as far as I needed.
 
Thanks Dave,

Is there a way with code? The sheet is not set up to use 2 cells for
one input (there will be 100s of cells in 10s of columns). Thanks
again,

Nick
 
Bonsour® (e-mail address removed) avec ferveur ;o))) vous nous disiez :
HI - How can Excel auto complete a string if a user only types the
first digit and then presses the Enter or arrow keys? For example, a
3-digit string of ‘444’ should be entered if a user enters a ‘4’ and
presses Enter. Likewise...if a user enters 1 and presses Enter, the
result should be 111; 2 + Enter = 222, and 3 + Enter = 333.

NOTE that if a user enters some other full, 3-digit string, like ‘421’
or '123,' the cell MUST retain the 421 or 123. Also note that this
will be a text field. THANK YOU!
Click to expand...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target Like "#" And Len(Target) = 1 Then
Target = 1 * (Target & Target & Target)
End If
End Sub

HTH
 
You could use an event macro like Paul suggested.

Right click on the worksheet tab that should have this behavior and select view
code. Paste this into the code window that you see. Change the code to match
the addresses that you need:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range
Dim myIntersect As Range
Dim myCell As Range

Set myRngToInspect = Me.Range("a1:a10,C1:C10,e9:e99")

Set myIntersect = Intersect(Target, myRngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

On Error Resume Next 'just fly by errors
Application.EnableEvents = False
For Each myCell In myIntersect.Cells
If myCell.Value Like "#" Then
myCell.Value = String(3, CStr(myCell.Value))
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

Be aware that macros -- event event macros -- will usually kill the clipboard
and clear the Undo/Redo stack.
 
Back
Top