Help with simple list shortcut

[americanmomo]

Folks,

I have made a list of 9 columns. I have about 400 cells of data and want to create a shortcut that allows somebody to automatically send the contents of a cell to one of the 9 columns as they choose. something like Control + 8 to send the text of a selected cell to column 8 in list form.

Help? Thanks!

 

[HalfAce]

Hi americanmomo, welcome to the board.
I'm afraid we're going to need a bit more info before being able to make any real suggestions.
Where is the cell you want to copy the data from?
When it gets 'sent' to the column of choice, where do you want it put? In the same row as where it's coming from? or in the next available row?

 

[americanmomo]

Sorry for the vagueness.

So I'm designing a rating setup for social science research. Essentially there will be 400 sentences, and various raters will decide whether those sentences go in 1 of 9 columns. So I want it to be super simple, so that a cell with a sentence to be rated can be sent to a column (1-9) with a simple shortcut. Does that help?

 

[HalfAce]

Well, a little, but not enough.
We still need to know where we're copying from (ie.) a specified cell or range somewhere?, or perhaps just the active cell at the time? or.....(?)

And we still need to know where to 'paste' the copied sentence. In the column of choice, yes, but where? In the same row the sentence is in originally? In the next available row? or....(?)

 

[americanmomo]

A specified cell. So there will be a list of sentences, one in each cell. The person will move their cursor over the next cell to highlight it, and do a quick shortcut (shorter than copy and paste) into the desired column, the next available cell in the column. Ideally it would be a list with a count function at the bottom.

 

[americanmomo]

Another way to approach is is whether there is a shortcut for dragging -- so instead of using the mouse to drag a cell to a desired location, can you just do a shortcut that tells it what column to send it to, to the next available cell in the column? Thanks.

 

[HalfAce]

OK, assuming a bit of vba is acceptable then how about one of these ideas to start from?

First off, we could come up with 9 different routines, and assign each one a different shortcut like Ctrl + (1 through 9) and have the user simply press the appropriate combination for where they want the sentence to go. (Would work but probably more than we need.)

A possibly simpler idea would be to use the before double click event of your sheet with a quick inputbox for the user to enter the column number of choice.

Below is an example that assumes your sentences originate in column A.
By double clicking any cell (with data) in column A, it brings up an inputbox. The user simply enters the column number of interest and hits OK (or enter.) The value of the cell they double clicked will copy & paste into the next available row of the column they specified.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'''Change "A:A" to the real range of your source sentences.
If Intersect(Target, Range("A:A")) Is Nothing Or Target = "" Then Exit Sub

Dim ColChoice&
ColChoice = Application.InputBox("Enter your destination column.", "Copy to which column?", Type:=1)
If Len(ColChoice) = 0 Or ColChoice < 2 Then Exit Sub
ActiveCell.Copy Cells(Rows.Count, ColChoice).End(xlUp)(2)

End Sub

Bear in mind this is just a jumping off point. Where your actual source sentences come from and where the actual destination columns are may be different which will require (1) my knowing where everything is and (2) tweaking to code to suit.

Hope it helps.

 

[americanmomo]

Thanks for your help!

Now here is the especially dumb question that will show you how little Excel I know. Where do I paste that code?

 

[HalfAce]

Oh, sorry! (It's not a dumb question at all...)
Right click the sheet tab, choose View Code and copy (from here) and paste into the white area on the screen. (This is the sheet module for that sheet.)
Press Alt + Q to close the vb editor and get back to your sheet to try it out.

 

[americanmomo]

I'm on 2008 for the Mac -- don't think I have the VB editor.