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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Coordinator[/TD]
[TD]Task #1[/TD]
[TD]Status[/TD]
[TD]Task #2[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Project#1[/TD]
[TD]Mark[/TD]
[TD]3/14/2017[/TD]
[TD]Expected[/TD]
[TD]4/20/2017[/TD]
[TD]Expected[/TD]
[/TR]
[TR]
[TD]Project#2[/TD]
[TD]James[/TD]
[TD]3/25/2017[/TD]
[TD]Done[/TD]
[TD]3/26/2017[/TD]
[TD]Expected[/TD]
[/TR]
[TR]
[TD]Project#3[/TD]
[TD]Jimmy[/TD]
[TD]3/24/2017[/TD]
[TD]Expected[/TD]
[TD]4/14/2017[/TD]
[TD]Expected[/TD]
[/TR]
</tbody>[/TABLE]

I want to return the following on a new sheet:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Coordinator[/TD]
[TD]Task Name[/TD]
[TD]Task Due Date[/TD]
[TD]Task Status[/TD]
[TD]Days until Due Date[/TD]
[/TR]
[TR]
[TD]Project #1[/TD]
[TD]Mark[/TD]
[TD]Task #1[/TD]
[TD]3/14/2017[/TD]
[TD]Expected[/TD]
[TD]-8[/TD]
[/TR]
[TR]
[TD]Project #2[/TD]
[TD]James[/TD]
[TD]Task #2[/TD]
[TD]3/26/2017[/TD]
[TD]Expected[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Project #3[/TD]
[TD]Jimmy[/TD]
[TD]Task #1[/TD]
[TD]3/24/2017[/TD]
[TD]Expected[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Project#3[/TD]
[TD]Jimmy[/TD]
[TD]Task #2[/TD]
[TD]3/28/2017[/TD]
[TD]Expected[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[MickG]

Try this for results on sheet1.

[COLOR="Navy"]Sub[/COLOR] MG23Mar14
[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 = Sheets("Data").Range("B1").CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * 10, 1 To 11)
nray(1, 1) = "Project Name": nray(1, 2) = "Project Code": nray(1, 3) = "Manager"
nray(1, 4) = "Project coordinator": nray(1, 5) = "Designer": nray(1, 6) = "Client Rep"
nray(1, 7) = "Project Rep": nray(1, 8) = "Task #n": nray(1, 9) = "Expected date"
nray(1, 10) = "Status of Task #n": nray(1, 11) = "Days until Deadline"
c = 1
[COLOR="Navy"]For[/COLOR] n = 3 To UBound(Ray, 1)
   [COLOR="Navy"]For[/COLOR] Ac = 16 To UBound(Ray, 2)
     [COLOR="Navy"]If[/COLOR] IsDate(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]If[/COLOR] DateDiff("d", Date, Ray(n, Ac)) < 8 And Not UCase(Ray(n, Ac + 1)) = "DONE" [COLOR="Navy"]Then[/COLOR]
         c = c + 1
          nray(c, 1) = Ray(n, 1)
          nray(c, 2) = Ray(n, 19)
          nray(c, 3) = Ray(n, 8)
          nray(c, 4) = Ray(n, 9)
          nray(c, 5) = Ray(n, 10)
          nray(c, 6) = Ray(n, 11)
          nray(c, 7) = Ray(n, 12)
          nray(c, 8) = Ray(1, Ac)
          nray(c, 9) = Ray(n, Ac)
          nray(c, 10) = Ray(n, Ac + 1)
          nray(c, 11) = DateDiff("d", Date, Ray(n, Ac))
       [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1").Range("A1").Resize(c, 11)
    .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]

It says subscript out of range on this line:
If DateDiff("d", Date, Ray(n, Ac)) < 8 And Not UCase(Ray(n, Ac + 1)) = "DONE" Then

 

[MickG]

Have a look at your returned sheet (Results shown on sheet1) to see what differences there are between it and you actual data sheet.
https://app.box.com/s/2lz9pxkoyebqc2x8o6uzjhiakogj25ll

 

[mbagz]

Actually, your code works beautifully on the Example file I provided. When I tried to use it on my actual sheet, it didn't work. I think it's because the first column actually starts out at J1, but I changed part of your code to :
Ray = Sheets("Data").Range("J1").CurrentRegion

Any idea why it still isn't working?

EDIT: Actually, I think I understand what is going on. The cells have formulas inside them that pull values from other excel files. Maybe the code isn't reading "DONE", but it is reading the path "C/documents/file/examples.xlsx....." COuld that be it?

 

[Rick Rothstein]

Here is something I tend to post when I see a thread progressing the way this particular thread has progressed...

For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[mbagz]

You are correct. It was completely my fault for simplifying the problem.

However, I do want to thank MickG for writing out all the code. I figured out a solution. There were two problems, and both were a result of my over-simplification.
1. The data on the sheet is grabbed from multiple excel sheets, so i'm pretty sure excel reads the file path as a value and not the actual value displayed.
2. Dates of 1/0/1990 were displayed in my sheet. I removed them and this fixed my problem.

Now, my question is, can excel actually ready the displayed value instead of the file path? and is there a way to automatically delete all dates showing 1/0/1990 at the begging of the script? Both of these are specific to my problem, but there has been so much help already so I'm very happy