Hi all.
I got a question regarding a file that I'm working on since yesterday.
I got this table:
I need the data to be like this:
So If a person has 3 OK's The code needs to return the column header and create a duplicate for every column header. I got this code now.
The code above creates all I need EXCEPT "I" column that is messed up. I know that I need a variable that counts the column where the OK is and return the header, but I don't know how to make it work.
Please help a bit.
I got a question regarding a file that I'm working on since yesterday.
I got this table:
I need the data to be like this:
So If a person has 3 OK's The code needs to return the column header and create a duplicate for every column header. I got this code now.
Code:
Sub BlankLine()Dim Col As Variant, coll As Long, LastRow As Long, i As Long, j As Long, StartRow As Long, LR2 As Long
Col = "a"
StartRow = 1
LastRow = Sheet1.Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
j = 0
s = 1
For i = 2 To LastRow
LR2 = Sheet2.Cells(Rows.Count, Col).End(xlUp).Row
coll = WorksheetFunction.CountIf(Sheet1.Range("I" & i, "AG" & i), "OK")
For j = 1 To coll
Sheet2.Range("A" & LR2 + j).Value = Sheet1.Range("A" & i).Value
Sheet2.Range("B" & LR2 + j).Value = Sheet1.Range("B" & i).Value
Sheet2.Range("C" & LR2 + j).Value = Sheet1.Range("C" & i).Value
Sheet2.Range("D" & LR2 + j).Value = Sheet1.Range("D" & i).Value
Sheet2.Range("E" & LR2 + j).Value = Sheet1.Range("E" & i).Value
Sheet2.Range("F" & LR2 + j).Value = Sheet1.Range("F" & i).Value
Sheet2.Range("G" & LR2 + j).Value = Sheet1.Range("G" & i).Value
Sheet2.Range("H" & LR2 + j).Value = Sheet1.Range("H" & i).Value
Sheet2.Range("I" & LR2 + j).Value = Sheet1.Range("H1").Offset(0, j).Value
s = s + 1
Next j
Next i
End With
Application.ScreenUpdating = True
End Sub
The code above creates all I need EXCEPT "I" column that is messed up. I know that I need a variable that counts the column where the OK is and return the header, but I don't know how to make it work.
Please help a bit.