selecting cells and getting their content into a string?

M

mocha99

Hi all

I have a spreadsheet (Excel 2007) with text values in columns A and B
(rows 1 to 120). Is there a way to put some code in column C (rows 1-to
120) so that each cell in column C shows either a tick box or a radio
button and then by launching a separate command (a button perhaps) i get
a string with the content of the cells in column A for which I have
checked the corresponding cell in column C?

Example, my spreadsheet has:
A B C
1 cat yellow
2 dog red
3 squirrel blue
4 bird pink

then I do the ticking in column C

A B C
1 cat yellow X
2 dog red
3 squirrel blue X
4 bird pink

then I press my button and i get (in the clipboard or in cell D1) this
string "cat,squirrel", so the spreadsheet now looks like:


A B C D
1 cat yellow X cat,squirrel
2 dog red
3 squirrel blue X
4 bird pink

Any idea or example on how ro do this?

Thanks!
 
R

Rick Rothstein

You can use this macro to do what you ask...

Sub MakeString()
Dim X As Long, StringOut As String
For X = 1 To 120
If Len(Cells(X, "C").Value) Then
StringOut = StringOut & Cells(X, "A") & ","
End If
Next
Range("D1") = Left(StringOut, Len(StringOut) - 1)
End Sub

To install the macro, press Alt+F11 to get into the VB editor, then click
Insert/Module from its menu bar and then copy/paste the above code into the
code window that appeared. Now, go back to your worksheet and place any
characters you want in Column C (you probably will want to use an X, but any
character or characters will work). Next, press Alt+F8, select MakeString
and click the Run button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top