Solved: Find and get contain of cells with multiple lines

[rescuede]

Hi all ,

Hope you can take a look at problem i must face at work .Any idea will be cheered up.


In an excel file i attach here is a sample part of the long list.http://rapidshare.de/files/34504245/Cellcontainfile.xls
I must write a macro to get the exact datas from worksheet and copy it to another one,and dont know how to get it exactly.



(This excel file is manually typed from one pdf file )

1. in the sheet decribes failure art of measurement with date ,when it is explored.Criteria bases on word "TCB".

If such failure art TCB is explored in any lines ,the macro will get production date and malfunction date and copy it to one sheet(exampleTCB failure).

But sometimes there are many failures for one product. It means cells in column "malfunction date" and "comment"contain many linesinc blank lines.So it should get only the exact malfunction date of "TCB" in column "Commment"

ex: the product in date 08.03.06 has two failuresTCB in 02.06.06
and 14.03.06 but not the other date.

Problem for me is such cells with multiple lines and blank rows.


2. the results should look like in the sheet example and it is sorted with month and year of production.

ex: all failures explored from 01.01.06 to 31.01.06 will be copied to range under the subject jan +"production year" or 01.06.The order of mal function date is not necessary.Is there any build in function to put a date to right order? Like 15.01.01 will be put in Jan 06.

Only production months contained in excel file with failure will be exported to result list


3.There will be an digramm for the result list of each month .How can i set it that all digramms are not overlapped themselves?

Thanks for help

[mdmackillop]

I can't access your file. You can post it here using Manage Attachments in the Go Advanced section

[rescuede]

http://rapidshare.de/files/34504245/Cellcontainfile.xls

in the link it has one more http://.U can just copy and paste the above link.

Thanks

[mdmackillop]

Copy of file

[mdmackillop]

I've not tried to set up as your output sheet, but here's what I think is your data
[vba]
Option Explicit
Sub ExtractData()
Dim x As String, tmp
Dim i As Long, j As Long, k As Long, Rw As Long
Dim ProdDate As Date, MalfDate As Date
x = Chr(10)
j = 3
Rw = Cells(Rows.Count, 9).End(xlUp).Row
For i = 4 To Rw
For k = 0 To UBound(Split(Cells(i, 9), x))
On Error GoTo NextCell
If InStr(Split(Cells(i, 9), x)(k), "TCB") Then
ProdDate = DateValue(Cells(i, 3))
MalfDate = DateValue(Replace(Split(Cells(i, 4), x)(k), ".", "/"))
Sheets("sheet1").Cells(j, 2) = ProdDate
Sheets("sheet1").Cells(j, 3) = MalfDate
j = j + 1
End If
Next
NextCell:
Next
Sheets("Sheet1").Activate
End Sub

[/vba]

[rescuede]

HI Mac,

i tried ur script and it run smoothly until some special cases occur errors.

In column "Malfunction date" some cells dont have date . But they have "TCB" failure in comment column or vice versus.

So if it is the case ,ur script cant find them or miss them -> When ur script runs in long excel fiel it give error message,but only in sample excel ideos not give error message but miss such above cases


I attatch here new data,and hope u can go around with it .

PS: when errors "TCB" have no date,can u set it to default like production date or watever?

and thanks for hellp till now.

[mdmackillop]

I'm getting a message that your attachment is corrupted. Can you check and post it again.

[rescuede]

Again

http://rapidshare.de/files/34660798/Downloads.rar
Srry cant uploadfile to forum some weird error message.

Dont even know why fiel is corrupted.Becoz i changed the file extension b4 sending it to my home email address.

PS:http://www.megaupload.com/?d=LL4J4VNH

[mdmackillop]

?

[rescuede]

Can u open it?

PS :Hmm the changing and rechanging file extension .xls is the reason of file corruption.i Dont have have it originally at home ad cant even open it at the moment.

I wil try to post it tommorow when im at work then.

Tenks for reply

[mdmackillop]

Still corrupted.

[rescuede]

http://rapidshare.de/files/34736089/...file280906.xls
http://www.megaupload.com/?d=YPM6VS3P

Hi Mac,

I create the excel file again and send u here the link.

I wrote some line myself but it still has has some bugs or is not optimized.You feel free to correct it.Can you check ur old script for some special cases i added in attachment?

Please take a look at my excel example first b4 u run to check the whole macro first?
Hope at this time file is not corrupted anymore.I dled and checked it once.

PS: for failure TCB which date is not found ,just init it to the production date or watever

Thanks

[rescuede]

H.....

[rescuede]

Did you find other way to go around with it MD?

[mdmackillop]

Can you add some explanation/comments as to how your code interacts. Where does it start? Otherwise I'll be all night just trying to comprehend it.

[rescuede]

I didnt complete my code.And it runs till now only a litlle bit.

Like at the beginning of thread. my code is splitted in 2 main sub.

All of codes are in "modul1" but not "module 1"

_ run1() uses ExtractData (customized version of urs) to get Date with TCB failure from failure sheet to failure summary sheet.Ur code does not cover all cases


_run2() writes malfunction date of failure summary sheet to each production month in each sheet ,which is predesigned like that.Then all malfunciton dates are sorted for each production month

_later i will add some lines of codes in run2() to create diagram based on malfunction dates in each production month.But it stilll has some bugs.It would be nice if u can add ur codes for creating diagramm too.

WOuld u take a look at sheet (**2004) ?.There is already an example layout which i want to get .

Thanks

[mdmackillop]

Try the following. I changed your methodology a bit, but I think it simplifies things a lot.
[vba]
Option Explicit

Sub run1()
Dim x As String
x = Chr(10)
ActiveWorkbook.Worksheets("Failure").Activate
ActiveWorkbook.Worksheets("Failuresummmary").Range("A:C").Clear
Call ExtractData(x, 9, "Failure", "Failuresummmary")
Allocate
Sorting
End Sub

'Extract data to FailureSummary sheet
Sub ExtractData(x As String, kritcol As Integer, sourcews As String, targetws As String)
Dim i As Long, j As Long, k As Long, lastRow As Long
Dim ProdDate As Date, MalfDate As Date
Dim lNr As Long
j = 3
ActiveWorkbook.Worksheets(sourcews).Activate
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 7 To lastRow
For k = 0 To UBound(Split(Cells(i, kritcol), x))
On Error GoTo NextCell
If InStr(Split(Cells(i, kritcol), x)(k), "TCB") Then
On Error GoTo NextCell
ProdDate = DateValue(Cells(i, 3))
On Error Resume Next
MalfDate = DateValue(Replace(Split(Cells(i, 4), x)(k), ".", "/"))
If Err.Number = 9 Then MalfDate = Cells(i, 3) 'Production date
Sheets(targetws).Cells(j, 1) = Cells(i, 1).Value
Sheets(targetws).Cells(j, 2) = ProdDate
Sheets(targetws).Cells(j, 3) = MalfDate
j = j + 1
End If
Next
NextCell:
Next
End Sub

'Copy data from Summary sheet to year sheets
Sub Allocate()
Dim Rg As Range, cel As Range
Dim Yr As Long, Mth As Long, Col As Long
Dim j As Long
'Clear old data
For j = 2004 To 2006
Sheets("Ausf?lle " & j).Rows("3:50").ClearContents
Next
'Get Month and Year of Production Dates; use to allocate to sheets/columns
With Sheets("Failuresummmary")
Set Rg = Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
For Each cel In Rg
Yr = Year(cel.Offset(, 1))
Mth = Month(cel.Offset(, 1))
Col = 1 + 5 * (Mth - 1)
cel.Resize(, 3).Copy Sheets("Ausf?lle " & Yr).Cells(Rows.Count, Col).End(xlUp).Offset(1)
Next
End With
End Sub

'Sort allocated data in ascending order of Fail date
Sub Sorting()
Dim i As Long, j As Long
Dim Rg As Range
For i = 2004 To 2006
With Sheets("Ausf?lle " & i)
For j = 0 To 11
'Check if any data
If Not .Cells(3, 1 + 5 * j) = "" Then
'Create sort range from data
Set Rg = Range(.Cells(3, 1 + 5 * j), .Cells(Rows.Count, 1 + 5 * j).End(xlUp)).Resize(, 3)
'MsgBox Rg.Address
'Rg.Select
If Rg.Rows.Count > 1 Then
Rg.Sort Key1:=.Cells(3 + 5 * j), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End If
Next
End With
Next
End Sub
[/vba]

[rescuede]

I will check it tommorrow on real excel file tomorrow and reply u back then.

Thanks for spending time till now

[mdmackillop]

There is a problem with your data. Because Column I is bottom aligned, it looks as though in Row 8 for example that TCB corresponds with 06.03.06, whereas it really aligns with the first value in D8. It would make sense to insist on a value in each line in column I and also the change the alignment to top.

[rescuede]

Hi MD
Ur script runs smoothly .Thanks for spending time to correct codes and giving me explaination.

Problem solved.