VBA Loop Selected Rows to One Cell with Comma's Appended

[gutermdp]

Hey All,

I have a two part question, the first being just a generic request for information that will hopefully alleviate the need for posts like this, and the second being an actual issue I'm trying to conquer.

So Question 1, I'm terrible with loops, can someone recommend a number of resources you found were best to help you get over the hump and use them effectively? Web, Books, whatever the media is fine.

Question 2, I have a list of data in from A1:A10, each item represents a color in my mock example. I want to have B1 populate with what would essentially be a concatenated list of values from my selected cells in Column A and append a comma after each.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Teal[/TD]
[TD]Teal, Blue, Green, Red, Orange, Yellow, Purple, Maroon, Magenta, Black[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Purple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maroon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Magenta[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Black[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I know how I can accomplish this with a number of formulas, but I'm trying to utilize loops/VBA/whatever would be most efficient to not have to keep creating custom formulas as we do this often at work to create strings for data searches.

Will look to execute with hotkey/button press, but I can handle that.

Thanks,
Dan.

 

[MickG]

Q(1) :- Practice !!
Q(2) try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Feb02
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
Ray = Application.Transpose(Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)))
[COLOR="Navy"]With[/COLOR] Range("B1")
    .Value = Join(Ray, ",")
    .WrapText = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[vds1]

Question 1 : Google

Question 2 : Using loops with comments

Sub Loop_Macro()
Dim Result As String
Dim I As Integer


'Assign First Value to Result
Result = Cells(1, 1).Value


'Loop from 2 to 10th row in column A and assign value to result value with previous result value
For I = 2 To 10
    Result = Result & "," & Cells(I, 1).Value
Next I


'Assign result to Cell B2
Range("B1").Value = Result


End Sub

 

[gutermdp]

Question 1 : Google

Question 2 : Using loops with comments

Sub Loop_Macro()
Dim Result As String
Dim I As Integer


'Assign First Value to Result
Result = Cells(1, 1).Value


'Loop from 2 to 10th row in column A and assign value to result value with previous result value
For I = 2 To 10
    Result = Result & "," & Cells(I, 1).Value
Next I


'Assign result to Cell B2
Range("B1").Value = Result


End Sub

Thanks, this works great.

Is it possible to make this dynamic, so rather then being based off of a range of cells it would be done in conjunction with activesheet.selection?

I'd expect the values to print to offset(0,1).

Thanks!

 

[vds1]

Thanks, this works great.

Is it possible to make this dynamic, so rather then being based off of a range of cells it would be done in conjunction with activesheet.selection?

I'd expect the values to print to offset(0,1).

Thanks!

Maybe something like this..

Sub Loop_Macro()
Dim Result As String
Dim I As Integer
[COLOR=#0000cd]Dim LastRow As Long[/COLOR]


[COLOR=#0000cd]'Find LastRow USED in column A[/COLOR]
[COLOR=#0000cd]LastRow = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]


'Assign First Value to Result
Result = Cells(1, 1).Value




'Loop from 2 to last row row in column A and assign value to result value with previous result value
For I = 2 To [COLOR=#0000cd]LastRow[/COLOR]
    Result = Result & "," & Cells(I, 1).Value
Next I


'Assign result to Cell B2
Range("B1").Value = Result


'Note : In excel 2010 Maximum character allowed is 32767.


End Sub