Hello all,
I would like to transpose some vertical data (timestamps) in worksheet 3 horizontally to worksheet 2.
Steps
1. If you can't find the date from worksheet 2 column A in worksheet 3 column F, nothing should happen.
2. If you can find the date, count the amount of times you find this date and copy the corresponding timestamps (to this date) to worksheet 2.
E.g. 5/07/2017 has 2 timestamps in worksheet 3, thus worksheet 2 should only show a value in column Time1 and Time2.
I don't really know how to loop through this or work with select case or so...
Here below my flawed attempt, but the offset is a bit off and it doesn't copy the right amount of timestamps.
Expected result:![]()
lastRow = ws3.Cells(Rows.Count, "F").End(xlUp).Row Set r = ws3.Range("F2:F" & lastRow) i = 2 Do While ws2.Cells(i, 1).Value <> "" For x = 1 To Application.WorksheetFunction.CountIf(ws3.Range("F:F"), ws2.Cells(i, 1)) If r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) Is Nothing Then ElseIf r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) >= 1 Then ws2.Cells(i, 6).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x, 1) ws2.Cells(i, 11).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 1, 1) ws2.Cells(i, 16).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 2, 1) ws2.Cells(i, 21).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 3, 1) ws2.Cells(i, 26).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 4, 1) ws2.Cells(i, 31).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 5, 1) End If Next x i = i + 1 Loop
Capture.JPG











LinkBack URL
About LinkBacks

Register To Reply
Bookmarks