Help with simple list shortcut

americanmomo

New Member
Joined
Jul 18, 2010
Messages
6
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Jul 18, 2010
Messages
6
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

New Member
Joined
Jul 18, 2010
Messages
6

ADVERTISEMENT

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

New Member
Joined
Jul 18, 2010
Messages
6
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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.
Code:
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

New Member
Joined
Jul 18, 2010
Messages
6
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,758
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top