Data enter into Cells VIA User Input

[pk2009]

I have a spreadsheet consisting of 200 - Door #'s from 1 - 200 - Each Door # is listed in a cell and I am trying to populate the cell directly below it with an ID # that should be entered VIA user inputbox.

Basically I am trying to have two fields for the user to input the ID # and the door # and click ok and the ID # would be displayed in the cell below the door #.

The other functionality I require is to be able to move ID # from door to door - example ID # 2356 from Door 2 to Door 4.

There is also a staging area for ID #'s so the user would enter an ID # and enter stage instead of a door # and that number should be placed under Staging Area. As there will be more than one entry for this area it should automatically enter it in the next available cell in that column.

Is this something that can be done VIA a Macro script


Cross posted at Ozgrid.com (http://www.ozgrid.com/forum/showthread.php?t=153932) - 3 days ago / no response - Hoping someone here might know of a way.

 

[venkat1926]

I presume that door no. is already entered in row no. 1 (horizontally)

to your first question try this macro

Sub id()
Dim door As Integer, id, cfind As Range
door = InputBox("type the door #")
id = InputBox("type the id corresponding to that door #")
Set cfind = Rows("1:1").Cells.Find(what:=door, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.Offset(1, 0) = id
Else
MsgBox "that door # is not available"
End If
End Sub

your second question is not clear. give a small example with small extract of data sheet.

 

[pk2009]

Thanks - That Macro works perfectly for entering an ID# and a door # based upon the door #'s being in the first row.

The actual door #'s are randomly located on the spreadsheet in the pattern of their physical layout in the building. Also once the macro is run it goes off the screen and you have to go and run it again to enter another ID #.

Is there a way to have the user input sections on the spreadsheet? So they can enter in multiple ID's and door #'s at a time?

With regard to the second and third functunality - say there was an ID # already stored in Door 2 and this ID # needed to be transferred to door 4. I need a user input where they can specify move door 2 to door 4 or door x to door x.

The third functionality is the staging area - in where a door # can only contain one ID # - the staging area needs to contain multiple ID #'s. So everytime one is put into the staging area they should list on the next open cell in that column under staging.

I wish I could attach a sample of the workbook - but I don't have access to post in this forum.

 

[venkat1926]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->


“once the macro is run it goes off the screen and you have to go and run it again to enter another ID #”

I do not know how you run the macro
Open the sheet
Click tools(menu bar)-macros-macro
In the macro window in the bottom choose “this workbook”: instead of “all open workbooks”
You see the macro id
Double click this id (or highlight id and click Run.)

In such a procedure the sheet remains on screen. If any more doubt post back

2. possible for user input more than one door # but requires major modification. Besides when you choose for e.g. three door numbers will it not be difficult to remember the id(s) for all the three door number. Rethink on this.

3. Shifting id to another door number requires another macro. Is it worth writing a macro. Is it easier to use cut and paste.

3. Staging area problem still not clear. There is an add in to post a sheet. See one of the sticky threadsat the top of the list of messages. Otherwise highlight small portion of the sheet and copy paste to notepad and copy notepad data to post. Try this. Straight copy paste form excel sheet to post give sometimes problem.