Find out if file attachment exists for each number 1

[chandlm]

Hi Guys,

I have a big project at work and need some help figuring out how to get this part to work.

I have a spreadsheet which looks like this:

Flt No. Date Route File Attachment
BY001 20030714 LTN-MAH .pdf
BY003 20030716 LGW-LGW MPL.txt
BY003 20030716 LGW-MAH .pdf
BY003 20030716 LGW-MAH CIF.txt
BY003 20030716 LGW-MAH CRW.doc
BY003 20030716 LGW-MAH FLT.doc
BY003 20030716 LGW-MAH MGR.txt
BY003 20030716 LGW-MAH MSS.txt
BY003 20030716 LGW-MAH SIS.doc
BY003 20030716 MAH-LGW MGR.txt
BY003 20030716 MAH-LGW MSS.txt
BY003 20030716 MAH-MAH MPL.txt
BY004 20030717 PMI-PMI MPL.txt
BY004 20030717 PMI-STN MGR.txt
BY004 20030717 STN-PMI .pdf
BY004 20030717 STN-PMI CIF.txt
BY004 20030717 STN-PMI FLT.doc
BY004 20030717 STN-PMI MGR.txt
BY004 20030717 STN-PMI MSS.txt
BY004 20030717 STN-PMI SIS.doc
BY004 20030717 STN-STN MPL.txt
BY007 20030712 LGW-IBZ .pdf
BY008 20030714 LTN-LPA .pdf
BY010 20030716 LGW-LGW MPL.txt
BY010 20030716 LGW-MIR .pdf
BY010 20030716 LGW-MIR CIF.txt
BY010 20030716 LGW-MIR CRW.doc
BY010 20030716 LGW-MIR FLT.doc
BY010 20030716 LGW-MIR MGR.txt
BY010 20030716 LGW-MIR MSS.txt
BY010 20030716 LGW-MIR SIS.doc
BY010 20030716 MIR-LGW MGR.txt


The problem is that I need to find out if there is a cif.txt file attachment in column d for each flight number and if not output the flight numbers missing this file to either a text file or new worksheet.

I would be appreciative of any assistance or ideas offered.



Matt

 

[chandlm]

Any Takers out there ?

Matt

 

[DrBowes]

This pastes them onto a new sheet. It does this by:

creating a collection containing all flight numbers.
removing those with a CIF.txt in column 4.
outputting them onto a new sheet.


Sub findwithoutCIF()
Dim Cln As New Collection
On Error Resume Next
For i = 2 To Cells(65000, 1).End(xlUp).Row
Cln.Add Cells(i, 1).Value, CStr(Cells(i, 1).Value)
Next i
For Each x In Cln
For i = 2 To Cells(65000, 1).End(xlUp).Row
If x = Cells(i, 1).Value And _
Cells(i, 4).Value = "CIF.txt" Then
Cln.Remove x
End If
Next i
Next x
Worksheets.Add
r = 1
For Each x In Cln
Cells(r, 1) = x
r = r + 1
Next x
End Sub

 

[chandlm]

DrBowes,

Thanks for the response, However when I run the macro it goes through and only adds a blank sheet, even though I know that it should contain at least 1 flight number without the CIF.txt attachment.

Any ideas ?

Thanks



Matt

 

[DrBowes]

That is very strange.

When I ran it on your example (in Excel 97) it gives BY001, BY007 and BY008.

When you run this part it should give all flight numbers. Does this part work?

Sub AllFlightNumbers()
Dim Cln As New Collection
On Error Resume Next
For i = 2 To Cells(65000, 1).End(xlUp).Row
Cln.Add Cells(i, 1).Value, CStr(Cells(i, 1).Value)
Next i
Worksheets.Add
r = 1
For Each x In Cln
Cells(r, 1) = x
r = r + 1
Next x
End Sub


Le me know and I will try to figure out what has happened.

 

[chandlm]

DrBowers,

I have tried the revised version and have the same problem. just a blank sheet being added. I am using excel 2000 if that makes a difference.



Matt

 

[Zathras]

DrBowes: My compliments on a very nice use of the collection object. I don't use it nearly enough when I probably should.

Matt: DrBowes code works just fine for me, too. I'm using Excel 2000 at the moment. Perhaps your data aren't in columns A, B, C, and D as implied by your post. If I insert a blank column in front of the test data, putting your data in columns B, C, D, and E, I also get a blank worksheet added.

 

[chandlm]

Zathras,

Thanks for the response.

I have checked through all of the columns and they are set up correctly as a , b , c , d.

Can't see why this is still causing a problem. I have added a debug.print to the line before it adds the flight numbers to the collection and it displays the cell value but doesn't appear to be adding it to the collection.

I am at a loss as I don't know nearly enough about collections.




Matt

 

[DrBowes]

Try this one without the On Error Resume Next and let me know what happens.

Will be a bit slower as it will add duplicate flight numbers top the original collection but may show why it is not adding the items to the collection.


Sub findwithoutCIF()
Dim Cln As New Collection
For i = 2 To Cells(65000, 1).End(xlUp).Row
Cln.Add Cells(i, 1).Value
Next i
For Each x In Cln
For i = 2 To Cells(65000, 1).End(xlUp).Row
If x = Cells(i, 1).Value And _
Cells(i, 4).Value = "CIF.txt" Then
Cln.Remove x
End If
Next i
Next x
Worksheets.Add
r = 1
For Each x In Cln
Cells(r, 1) = x
r = r + 1
Next x
End Sub

 

[chandlm]

DrBowes,

I have finally managed to get it to work.

Finally worked out that I had put the macro in the sheet1
So that when the macro got to adding the new sheet it lost the values in the collection.Hence 1 new blank sheet.

Appologies for not spotting this sooner and thanks for your code.(ahem!. it now works like a dream.)

Have a star for your troubles and excellent code. It was worth the aggrovation for getting me started with collections.





Matt

 

[Luis939]

DrBowes or anyone else, perhaps you could also help me, it basically has to do with the collection object also. Lets say for each item in my collection, in this case my items are worksheets that have met a certain condition. lets say each of these items has a certain cell like A1 containing a number that I can store in a variable. Whenever I encounter a worksheet in my collection and see the value in cell A1, i want to make sure that that value was not repeated in any of my previous worksheets. If it is then simply go onto the next worksheet. so in simple terms i just want to be able to make sure that no sheets have the same value in whatever range i decide, thanks!!