+ Reply to Thread
Results 1 to 4 of 4

Autocomplete cells

  1. #1
    Registered User
    Join Date
    09-08-2010
    Location
    England Liverpool
    MS-Off Ver
    Excel 2007
    Posts
    1

    Autocomplete cells

    Hi.

    In column "A" I'm scriving values, for example prices.

    I would like to do it faster. I found VBA code (unfortunately don't know author) which helps me much.

    I have list of prices, for example 2,99 3,99 etc.

    When I scrive "2" or "3" and hit enter, vba automatically insert "2,99" "3,99"

    Problem is when I have prices for example 2,99 and 22,99.

    I would like to scrive 2,9 or 22,9 and let vba to fill rest but doesn't recognize ","

    Is there any solution or other code which will serve it?
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Sub "autocompletes" data entered into column A using a source table on a different worksheet. If more than one match is
    ' found, the user is allowed to continue entering characters until a unique match is found. If no matches are found, the
    ' data is accepted as entered. ALT + Enter, Enter to force the macro to accept data as entered. The sub is triggered by
    ' the Enter key.
    Dim cel As Range
    Dim match1 As Range
    Dim match2 As Range
    Dim rg As Range
    Dim targ As Range

    '***Please adjust the next two statements before using this code!***
    Set targ = Intersect(Target, Range("A:A")) 'Watch the cells in column A
    Set rg = Worksheets("Source data").Range("B:B") 'Use named range AutoCompleteText for "autocomplete" info

    If targ Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo errhandler 'If code encounters an error, turn events back on

    For Each cel In targ
    If Not IsError(cel) Then
    If cel <> "" And Right(cel, 3) <> Chr(10) Then
    Set match1 = Nothing
    Set match1 = rg.Find(cel & "*", lookat:=xlWhole, MatchCase:=False) 'Match is case insensitive
    If Not match1 Is Nothing Then
    Set match2 = rg.FindNext(after:=match1)
    If match2.Address = match1.Address Then 'Code is fooled by identical strings in two cells
    cel = match1 'Only one match found. Use it to "autocomplete" the cell
    Else 'More than one match found. User must enter more data. Return to "Edit" mode
    cel.Activate
    'Application.SendKeys ("{F2}") 'Begin editing after last character entered
    End If
    Else 'No matches found. Do not change entered text
    End If
    Else 'Strip the line feed from the end of the text string
    If cel <> "" And Right(cel, 3) = Chr(10) Then cel = Left(cel, Len(cel) - 1)
    End If
    End If
    Next cel

    errhandler: Application.EnableEvents = True
    On Error GoTo 0
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Autocomplete cells

    Hi johntiber

    Welcome to the Forum

    Please wrap your code in code tags, before the moderators get you...

    Forum rules
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

    Cheers
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Autocomplete cells

    Cross posted here: http://www.mrexcel.com/forum/showthread.php?t=516482

    johntiber please read this and note the forum rules regarding cross posting: http://www.excelguru.ca/node/7

    Dom
    Last edited by Domski; 12-20-2010 at 10:08 AM.
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Autocomplete cells

    Johntiber, please read the forum Rules before posting again
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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