Auto fill text entry

[ashish514]

Ok, maybe I'm asking for too much here but i'm always amazed by how much excel can do and also by how much mrexcel forum can help.

I am looking for something similar to auto fill option on web pages. For example if i have three columns named city, state, and country, currently when i enter a certain city which has been entered earlier, excel automatically shows the name of the city based on the first few letters typed. Is there a way by which when I enter the name of the city, it also shows the name of the state and country in the next two columns automatically??? Any option, formula, macro or anything else???

 

[Jerry Sullivan]

Hi ashish514,

You could try the Worksheet_Change event code below.

It assumes that your City, State, Country fields are in in Columns A,B,C.
If a City matches an existing City entry in Column A, it will autofill the State and Country for the latest entry.

Copy the code into the Sheet Code Module (not a Standard Module) of the sheet in which you want it to run.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sAddress As String
    Dim cFound As Range
    
    sAddress = "A:A"
    If Intersect(Target, Range(sAddress)) Is Nothing Or _
        Target.Cells.Count > 1 Then Exit Sub
 
    With Range(sAddress)
        Set cFound = .Find(What:=Target, After:=Target, _
            LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If cFound.Address = Target.Address Then Exit Sub
        '---match found, fill values
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Target(1, 2) = cFound(1, 2)
        Target(1, 3) = cFound(1, 3)
    End With
        
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Smitty]

If you use an ActiveX ComboBox you'll have that functionality.

Set the ListFillRange to your range, and make sure to set the column count to 3 to show all three columns in the CB.

HTH,

 

[ashish514]

@Jerry- Yeah, it works. Thanks a lot. But as i am not much into programming (only a little bit of C), and so i just can't figure out how to modify it when these 3 columns are not consecutive columns and the references are not exactly A,B and C. I guessed that much that I have to change the definition of sAddress from "A:A" to the reference of my city column. Another (not so confident)guess is that i will have to chage the column reference nos. in following lines:
Target(1, 2) = cFound(1, 2)
Target(1, 3) = cFound(1, 3)

And what if i have to add another column which fills itself just like state and country.
Kindly help on this. Also, is it necessary that my city column should be on left of the other columns for this formula to work??

@Smitty- I've never used it before, actually I never knew that such an option existed in excel. I will learn how to use it and after that i'm sure it will be easier than writing macros. Thanks..

 

[Jerry Sullivan]

Another (not so confident)guess is that i will have to chage the column reference nos. in following lines:
Target(1, 2) = cFound(1, 2)
Target(1, 3) = cFound(1, 3)

And what if i have to add another column which fills itself just like state and country.
Kindly help on this. Also, is it necessary that my city column should be on left of the other columns for this formula to work??

This is one way to reference a cell in relation to another cell reference. It's similar to Range.Offset(no_rows,no_columns)

Let's say your City field is Column C instead of A, and that you enter a San Diego in Cell C41. We can reference C41 as Target(1,1) and other Cells in relation that.

Examples:
D41=Target(1,2)
C41=Target(1,1)
B41=Target(1,0)
A41=Target(1,-1)
C31=Target(-10,1)

These examples show that the City doesn't have to be to the left of the other columns.

 

[ashish514]

Got it, thanks a ton

 

[Jerry Sullivan]

You're welcome ashish

Smitty, Could you explain your suggestion a little more?
It would be great to have a drop down combobox from which to make multi column selections.

How would that work in the OP's scenario? The things I'm not following are:
1. How would the combobox serve as the data entry for each new row of data? Would you need to have a separate combobox in each row to make it act like a DataValidation dropdown list?
2. Would the ListFillRange have to be a Dynamic Named Range in order to not have duplicates and to resize as the list grows?