Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub Exercise() ' ' to read data from file tasks.xls and
- Dim Arr As Variant, Arr1 As Variant ' feed the task name for the person
- Dim iRow As Integer ' in a month in this file
- Dim iCol As Integer
- Dim i As Integer, x As Integer
- Dim name As String
- 'name = Cells(1, 1).Value
- Arr = Workbooks.Open("E:tasks.xlsx").Sheets("Sheet1").Range("B1:E1").Value
- Arr1 = Workbooks.Open("E:tasks.xlsx").Sheets("Sheet1").Range("B2:E2").Value
- Sheets(1).Cells(1, 1).Select ' go to beginning cell
- For i = 1 To Arr1(1, 1)
- Cells(6, 4 + i).Value = Arr(1, 1)
- a = i + 4
- Next i
- For i = 1 To Arr1(1, 2)
- Cells(6, a + i).Value = Arr(1, 2)
- b = a + i
- Next i
- For i = 1 To Arr1(1, 3)
- Cells(6, b + i).Value = Arr(1, 3)
- C = b + i
- Next i
- For i = 1 To Arr1(1, 4)
- Cells(6, C + i).Value = Arr(1, 4)
- d = a + i
- Next i
- Do While ActiveCell.Row <> Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
- ' some times i get infinte loop
- ActiveCell.Offset(2, 0).Select ' span till the last
- name = ActiveCell.Value ' non empty row
- Arr = Sheets(1).Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 5)).Value
- Arr1 = Sheets(1).Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(1, 5)).Value
- With ThisWorkbook.Sheets(3) 'algorithm to search the name ' positon in this excel file
- Dim findrow As Range
- Set findrow = .Range("A:A").Find(What:=name, LookIn:=xlValues)
- iRow = findrow.Row ' required row where name is found
- For i = 1 To Arr1(1, 1)
- Cells(iRow, 4 + i).Value = Arr(1, 1)
- a = i + 4
- Next i
- For i = 1 To Arr1(1, 2)
- Cells(iRow, a + i).Value = Arr(1, 2)
- b = a + i
- Next i
- For i = 1 To Arr1(1, 3)
- Cells(iRow, b + i).Value = Arr(1, 3)
- C = b + i
- Next i
- For i = 1 To Arr1(1, 4)
- Cells(iRow, C + i).Value = Arr(1, 4)
- d = a + i
- Next i
- Loop
- End Sub
- End with
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement