Use VBA to create a list after scanning data

[mbagz]

Hi All,

I have been trying to wrap my head around how to write this, but I need help.

Basically, I want to scan a large set of data (mostly dates) and return upcoming data + their respective tasks. The tricky thing is that I don't want to return tasks that are already "done". Below is a sample of my data:

Project Name	Project Coordinator	Task #1	Status	Task #2	Status
Project#1	Mark	3/14/2017	Expected	4/20/2017	Expected
Project#2	James	3/25/2017	Done	3/26/2017	Expected
Project#3	Jimmy	3/24/2017	Expected	4/14/2017	Expected

<tbody>
</tbody>

I want to return the following on a new sheet:

Project Name	Project Coordinator	Task Name	Task Due Date	Task Status	Days until Due Date
Project #1	Mark	Task #1	3/14/2017	Expected	-8
Project #2	James	Task #2	3/26/2017	Expected	4
Project #3	Jimmy	Task #1	3/24/2017	Expected	2
Project#3	Jimmy	Task #2	3/28/2017	Expected	6

<tbody>
</tbody>

For the new list on the new sheet, I need to display any dates past due + any due dates coming up in the next week. Is this possible with VBA? or is there a better way to automatically sort the data and do this?

I appreciate any help, or any direction anyone can provide on this for me.

 

[mbagz]

Bump, can anyone provide any info I can read up on to figure it out myself even? or help with the code?

 

[MickG]

Why is there only one line in Results for "Mark" and why Does the date not match in the second line for "Jimmy"

 

[mbagz]

Why is there only one line in Results for "Mark" and why Does the date not match in the second line for "Jimmy"

Ah I see your confusion and I should have made it more clear in my post.

The results should only have Tasks that are upcoming within the next 7 Days or past due. Any with the status "Done", but filling the criteria for the 7 days should not be in the list.

To be honest, coding the logic behind this in VBA is not what's tripping me up, but what is tripping me up is how to scan all the data, and return relative Tasks Name, Project Coordinator, and Project Names to the found data.

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG22Mar00
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * 2, 1 To 6)
nray(1, 1) = "Project Name": nray(1, 2) = "Project Coordinator": nray(1, 3) = "Task Name"
nray(1, 4) = "Task Due Date": nray(1, 5) = "Task Status": nray(1, 6) = "Days to due Date"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
   [COLOR="Navy"]For[/COLOR] Ac = 3 To UBound(Ray, 2) [COLOR="Navy"]Step[/COLOR] 2
      [COLOR="Navy"]If[/COLOR] Ray(n, Ac + 1) = "Expected" And DateDiff("d", Date, Ray(n, Ac)) < 8 [COLOR="Navy"]Then[/COLOR]
         c = c + 1
          nray(c, 1) = Ray(n, 1)
          nray(c, 2) = Ray(n, 2)
          nray(c, 3) = Ray(1, Ac)
          nray(c, 4) = Ray(n, Ac)
          nray(c, 5) = Ray(n, Ac + 1)
          nray(c, 6) = DateDiff("d", Date, Ray(n, Ac))
       [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mbagz]

Try this for results on sheet2.

[COLOR=Navy]Sub[/COLOR] MG22Mar00
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * 2, 1 To 6)
nray(1, 1) = "Project Name": nray(1, 2) = "Project Coordinator": nray(1, 3) = "Task Name"
nray(1, 4) = "Task Due Date": nray(1, 5) = "Task Status": nray(1, 6) = "Days to due Date"
c = 1
[COLOR=Navy]For[/COLOR] n = 2 To UBound(Ray, 1)
   [COLOR=Navy]For[/COLOR] Ac = 3 To UBound(Ray, 2) [COLOR=Navy]Step[/COLOR] 2
      [COLOR=Navy]If[/COLOR] Ray(n, Ac + 1) = "Expected" And DateDiff("d", Date, Ray(n, Ac)) < 8 [COLOR=Navy]Then[/COLOR]
         c = c + 1
          nray(c, 1) = Ray(n, 1)
          nray(c, 2) = Ray(n, 2)
          nray(c, 3) = Ray(1, Ac)
          nray(c, 4) = Ray(n, Ac)
          nray(c, 5) = Ray(n, Ac + 1)
          nray(c, 6) = DateDiff("d", Date, Ray(n, Ac))
       [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks for the help. To be honest, I guess I am not familiar with the syntax as much as I thought. The code didn't work for me and I think it's because the number of projects actually expands to around 50 and is variable. The Task #s are also expand and are around 30.

Is there a way to have variable number of projects to scan vertically and variable number of tasks to scan vertically?

 

[mbagz]

Sorry don't know how to edit comments.

In addition to the above comment, there are additional columns between my target columns. For Example, the data looks more like:

Project Name	Project Type	Site	Cost	Notes	Project Coordinator	Task #1	Status	Task #x	Status #x
Project #1
Project#2
Project #y

<tbody>
</tbody>

Stuff like the site, cost, and other columns are not important for me to grab info from. However, I do need to find data upcoming in 8 days with status "Expected" or "Deadline Expires Soon" or "Deadline Expired", and then grab the corresponding Project #y, project coordinator, Task #x and Status #x

Is this possible?

 

[mbagz]

bump bump

 

[MickG]

Please post your file Example using https://app.box.com/files (Free files haring) or similar showing data and expected results

 

[mbagz]

Here is the link: https://app.box.com/s/8rfr5pot1xowi3adzpjjix8bh00hno8t

Basically, this is the general layout of my data, and in the "Expected Results" is the data I want to find. I appreciate even if you could provide any basic code. Thank you again so much!