| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 53957 | 2005-02-01 00:51:00 | "XL brainteaser" | B.M. (505) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 320011 | 2005-02-01 23:16:00 | Hi Foxy, are you talking about AutoComplete - this option is turned on via Tools|Options|Edit tab|Enable AutoComplete for cell values . Unfortunately this only works when the list is directly above the cell your typing in rather than anywhere within the sheet . I believe some custom code could achieve this but I would have to give it some thought on the best way to tackle the problem . Im not sure this is what Bobs talking about though . Hi Parry, Yes, I am talking about AutoComplete and it works fine for me, most of the time . The list is directly above the cell I am typing in and apart from when the entry is waaaaay above where I am typing it normally works . What I was musing over, and don't think it would be possible to easily do, if at all, is instead of the AutoComplete filling in the cell for me it would jump to that cell that has the entry I am about to type in . As it is now, when it AutoCompletes an entry it indicates that somewhere above there is already an entry for that person's name/address or whatever and I then do a Ctrl+F to find it in order to add more information in another cell associated with that entry . Does that make sense to you? It's not a big deal and if it didn't have a quick and simple solution I wouldn't bother but I have discovered lots of neat tricks that Excel can do from reading yours and others replies to questions here that I never dreamed it can do so if I don't ask I'll never know . :D |
FoxyMX (5) | ||
| 320012 | 2005-02-01 23:44:00 | Hi FoxyMX, I was thinking you were meaning using AutoComplete when your not actually in the same column as the list of values. Thats damn hard to figure out as theres no events to monitor while your typing in a cell, only when youve finished typing in the cell. However, selecting a cell that matches is pretty easy with code. One question though, are the cells above unique? If not, how do you decide which cell to warp to? Heres some code with the following assumptions:- 1. You only want to look above the current cell 2. You want to clear the contents of the cell if there is an autocomplete match 3. If theres a match select the first cell (from top to bottom) that matches the value entered. This is using a Worksheet_Change event that runs after you press enter when entering a value into a cell. To place the code do the following:= 1. Right click the sheet tab and select View Code 2. Paste code in right hand window and Press Alt+Q to Exit the Visual Basic Editor Private Sub Worksheet_Change(ByVal Target As Range) Dim StrVal As String 'Exit procedure if more than one cell changes If Target.Cells.Count > 1 Then Exit Sub 'Exit procedure if cell is in row 1 If Target.Cells.Row = 1 Then Exit Sub 'Set a string variable returning autocomplete value StrVal = Target.AutoComplete(Target.Text) 'if string variable length > 0 then theres a match If Len(StrVal) > 0 And StrVal <> "" Then 'Select the matching cell Range(Cells(1, Target.Column), Target).Find( _ What:=Target.Text, After:=Target, LookIn:=xlValues).Select 'Clear contents of the cell that was just changed Target.ClearContents End If End Sub EDIT: Damn I stuffed up the code. Ive updated the code above so hopefully this will work properly. |
Parry (5696) | ||
| 320013 | 2005-02-02 19:36:00 | Hi Parry, Many thanks for taking the time to offer a solution to my query but I think it is going to be more trouble than it is worth . My worksheet is not set up and used as you assumed so I believe that it would probably be quite complicated to do what I was thinking . Never mind, I have another query that you are most likely to be able to solve and will start another thread on it if an idea that I have does not work out . :p |
FoxyMX (5) | ||
| 320014 | 2005-02-02 21:21:00 | Alot can be achieved with code. For instance after moving to the cell above it could go along the row and select the next empty column etc. However, you need to be exact about what you require and how this is applied in different situations. Whatever logic you use yourself to determine where to enter the data may be able to be applied using code, depending upon how clear the steps are. ;) | Parry (5696) | ||
| 1 2 | |||||