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:
Sub to amend to:
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).
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).
VBA Code:
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