Loop through Columns and Change Row Values to be of the respective Column

[ShuStar]

Hi all,

The Ask: For each column, starting from D3 to n number of Columns, replace the values of Y in each row reflect that of the column header.
ie if D3 Column header is Banana and D4 has Y, then it should replace D4 to be Banana, but dynamically do this as the number of Columns and Rows always change (ie can sometimes be 4 or 10 columns, and rows can be 5-100+ rows)


Current view:

ID	Name	Count	Banana	Apples	Nectarine	....to nColumn
12345	John	1	Y
12345	Sara	4	Y	Y	Y	Y
12345	Alex	3	Y	Y		Y
...to nRow	###	###	###	###	###	###

Sub to amend to:

ID	Name	Count	Banana	Apples	Nectarine	...to nColumn
12345	John	1	Banana
12345	Sara	4	Banana	Apples	Nectarine	...to nColumn
12345	Alex	4	Banana	Apples		...to nColumn
...to nRow	###	###	###	###	###	###

My code where I can't seem to get passed Range(startcolumn, "D"&lastrow).select as it keeps erroring out here.

Any help to resolve this would be greatly appreciated (ie how the code should dynamically scan each column, do a find and replace if the value contains Y to reflect that of the Column name, and then move onto the next column and do the same).

Sub changeYtorespectiveColumnName()



Dim lastrow As Long

Dim lastcolumn As Long

Dim startcolumn As Variant

Dim startingRange As Range

Dim InbtColumn As Long

Dim selectrange As Long

Dim i As Integer



Dim sht As Worksheet



Set sht = Sheets("Matrix")



lastrow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row - 1

lastcolumn = sht.Cells.SpecialCells(xlCellTypeLastCell).Column

startcolumn = Cells(3, 4).Range

Range(startcolumn, "D" & lastrow).Select

startingRange.Select



'For each column

For j = 1 To lastcolumn



'scan through each row and if the value equals Y, then replace it with the title of the column header

For i = 1 To lastrow





Next i



Next j







End Sub

 

[jolivanes]

Thanks for letting us know.
Next time I check a little closer and leave the not needed lines out!!!!!
As you might have noticed, the lr line is unnecessary.

 

[ShuStar]

JLGWhiz - yours is working too. thanks for this.

To All - the final thing I need to do here is to concatenate the row into a new column. Any ideas on how i can achieve this?

Ultimately, for each row starting from D3 to nth Column, I want to insert a new column to my table after Column C and just concatenate that row.

There may be a better way to do it without changing Ys to Column Header name etc but the flow in my head is:
-after changing the Ys to that of the name of column header then
-insert a new column after column C, which becomes new Column D (purpose for holding concatenated values) and then concatenate the rows from Column E to nth Column
-should look like "Banana, Apples, Lemon" and not "Banana, Apples , , Lemon" as there may be blank cells from the start column to end column - example below)


Example:

Currently looks like

ID	Name	Count	Banana	Apples	Nectarine	Lemon	...to nColumn
12345	John	1	Banana
12345	Sara	4	Banana	Apples	Nectarine		...to nColumn
12345	Alex	3	Banana	Apples		Lemon	...to nColumn
...to nRow	###	###	###	###	###		###

Change to (note new Concatenated column added):

ID	Name	Count	Concatenated	Banana	Apples	Nectarine	Lemon	...to nColumn
12345	John	1	Banana, Apples	Banana
12345	Sara	4	Banana, Apples, Nectarine	Banana	Apples	Nectarine		...to nColumn
12345	Alex	4	Banana, Apples, Lemon	Banana	Apples		Lemon	...to nColumn
...to nRow	###	###		###	###	###		###

Thanks inadvance Gurus, appreciate the help.

 

[JLGWhiz]

I believe that is outside the scope of the original post and forum guidlines indicate the need for a new thread to be started for the concatenate part.
Thanks for your feedback on this post.
regards, JLG

 

[ShuStar]

Thank you, will do.