Using a temporary list and searching for entries

[rowlandrat]

Hi,

I have a spreadsheet which contains tasks and subtasks. A parent task can have 1 or more subtasks. Each task has to be completed several times a week, but only the parent task is reported on. I have a piece of code that extrapolates out the tasks based on their frequency and pastes the data to a new sheet. However, what I need to do is only paste the data where the parent task has not already been recorded.

In normal code I would try to create a temporary table as i traverse down through the list, filling it with the parent task names. With each row I would check to see if the parent task existed in that list, if not I would add it to the list and paste the line into the new sheet, if it did then I would skip the row.

Can someone tell me how this might be possible in VBA?

The scenario and code I have is below.

SHEET 1:
Parent Task: Child Task: Frequency: Owner:
Report: Defects: Daily: Mike
Issues: Weekly: Niall
Status: Monthly: Edel
Report: Risks: Daily: Noel
Budget: Weekly: Tom

SHEET 2:
Weekly = 4
Daily = 20
Monthly = 1

SHEET 3:
I now want the Daily task to be repeated 20 times, the 2 Weekly tasks 4 times each and the Monthly task only once. The order is not important, but in the case of Reports, this should only be repeated 20 times (as its Daily) even though it has 2 sub tasks.

The code so far is:

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim cell As Range
Dim freq As Range
Dim cr As Range
Dim rep As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
Set freq = sh1.Range("B2", sh1.Range("B2").End(xlDown))
For Each cell In freq
rep = sh2.Range("A:A").Find(cell.Value).Offset(0, 1).Value
Set cr = sh3.Range("A" & sh3.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.copy sh3.Range(cr, cr.Offset(rep - 1, 0))

Next cell

 

[rowlandrat]

Or even if someone could help me by showing me how to do the following in VBA:

i = 1
For i to UBound(myArray)
Check if myArray(i) = ParentTask.value
If myArray(i) = ParentTask.value Then
Exit loop
Else UBound(myArray) + 1 = ParentTask.value
i = i+1
End