Read qualifying column names and paste, separated by commas, in designated cell
Hello,
Was hoping that someone could give me a hand with this. Please see the attached XLS to make sense of the cell IDs provided.
I am in need of a macro that will scan a worksheet row by row, noting the column names (found in I2 to AQ2) in a specific horizontal span of cells (I to AQ) that hold (any) data. The macro will then paste these column names in a designated cell on each corresponding row (always found in column F), separating the column names with commas.
I am trying to do this for multiple worksheets containing ~100 rows - the example attached is just a quick demo of what I'm hoping to achieve. Also, these worksheets are contained in one big workbook, so ideally I'd like to be able to run the macro once and have it apply to every sheet in that workbook (they all have the same layout - the only big difference is the number of rows).
If the Total Scenes part at the bottom of the sheet is problematic in getting this to work, it's fine to remove it.
Thanks in advance,
Ian
Last edited by thump4r; 12-10-2009 at 01:34 PM.
A lot of loops, but give this a try:
Sub x() Dim rng As Range, i As Long, j As Long, r As Long, ws As Worksheet For Each ws In Worksheets With ws For r = 3 To .Range("A2").End(xlDown).Row Set rng = .Range("I" & r).Resize(, 34).SpecialCells(xlCellTypeConstants).Offset(2 - r) For i = 1 To rng.Areas.Count For j = 1 To rng.Areas(i).Count .Range("F" & r) = .Range("F" & r) & "," & rng.Areas(i).Cells(j) Next j Next i .Range("F" & r) = Right(.Range("F" & r), Len(.Range("F" & r)) - 1) Next r End With Next ws End Sub
Hi, Try the attached:-
Regards Mick
Hey guys, thanks very much for your help - I've managed to get it working!
I love this forum :D
Best,
Ian
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks