PDA

View Full Version : [SOLVED] Transpose date from column name



Valentinas
12-05-2017, 01:18 AM
I have a file containing users for the year 2018 and the sign of employment is 1.
How do I come up to get a score on the Result (example) sheet.


There must be a start and end date, if we choose one day - the start and end dates coincide.


Maybe you have thoughts?

makako
12-05-2017, 10:35 AM
Something like this?


Sub Results()
Dim lvRangeUsername As Range, lvRangeDate As Range
Dim lvRangeUsernameResult As Range, lvRangeDateResult As Range
Dim lvPeriod As Integer

Set lvRangeUsername = Sheets("Data").Range("A2:A3")
Set lvRangeDate = Sheets("Data").Range("B1:O1")
Set lvRangeUsernameResult = Sheets("Result").Range("A2")
Set lvRangeDateResult = Sheets("Result").Range("B2")

lvRangeUsernameResult.CurrentRegion.Offset(1).ClearContents

lvCellU = 1
While lvCellU <= lvRangeUsername.Rows.Count
lvCellD = 1
While lvCellD <= lvRangeDate.Columns.Count
If Cells(lvRangeUsername.Cells(lvCellU).Row, lvRangeDate.Cells(lvCellD).Column) = 1 Then
lvPeriod = 1
While Cells(lvRangeUsername.Cells(lvCellU).Row, lvRangeDate.Cells(lvCellD).Column + lvPeriod) = 1
lvPeriod = lvPeriod + 1
Wend
lvRangeUsernameResult = lvRangeUsername.Cells(lvCellU)
Set lvRangeUsernameResult = lvRangeUsernameResult.Offset(1)
lvRangeDateResult = lvRangeDate.Cells(lvCellD)
lvRangeDateResult.Offset(, 1) = lvRangeDate.Cells(lvCellD).Offset(, lvPeriod - 1)
Set lvRangeDateResult = lvRangeDateResult.Offset(1)
lvCellD = lvCellD + lvPeriod - 1
End If
lvCellD = lvCellD + 1
Wend
lvCellU = lvCellU + 1
Wend

End Sub

Valentinas
12-10-2017, 11:39 PM
Thanks :)