need help in split macro (VBA)

[lynn8566]

Dear all

I have a huge amount of conference record which use to write down meeting content, like shown below (sorry I have changed the content)

Excel Workbook
	A	B	C	D	E	H	I
4	TOP	Topic	Meeting minutes	Type	responsible/contact	until	attachments & links
5	*	*	*	*	*	*	*
6	2006-09-01	1th meeting MV C221 / BCF	Building 11, 3th floor, room 2.A999	*	*	*	*
7	1	requirement sections and responsibilities	Der Stuttgarter Gemeinderat ist drauf und dran, seine massive Sparpolitik der vergangenen zwei Jahre nachhaltig zu lockern. Manfred Kanzleiter, seit vielen Jahren der SPD-Haushaltsexperte, hatte mit einem Antrag im Februar den Stein ins Rollen gebracht.	AA	ADEGruen	2006-09-182006-09-18	Requirement_sections
8	2	styling data	Doppelhaushalt fr 2012/13 gehe, so kndigte Manfred Kanzleiter an, werde die st?dtische Personalpolitik "ein zentrales Thema sein".Dieses Glasdach l?sst sich auf Knopfdruck hell oder dunkel schalten. Hell ist es fast v?llig durchsichtig und bietet auch bei kalter Witterung ein Open-Air-Erlebnis.	VA	AllRot	2006-09-222006-09-22	*
Sheet1

information in column C to E is correlative.

now we want to separate each records and responsible person to a new row.

here is an example sheet

Excel Workbook
	A	B	C	D	E	F	G
1	TOP	Topic	Meeting minutes	Type	responsible/contact	until	attachments & links
2	*	*	*	*	*	*	*
3	2006-09-01	1th meeting MV C221 / BCF	Building 11, 3th floor, room 2.A999	*	*	*	*
4	1	requirement sections and responsibilitie	Der Stuttgarter Gemeinderat ist drauf und dran, seine massive Sparpolitik der vergangenen zwei Jahre nachhaltig zu lockern.	A	ADE	2006/9/18	Requirement_sections
5	1	requirement sections and responsibilitie	Manfred Kanzleiter, seit vielen Jahren der SPD-Haushaltsexperte, hatte mit einem Antrag im Februar den Stein ins Rollen gebracht.	A	Gruen	2006/9/18	*
6	2	styling data	Doppelhaushalt fr 2012/13 gehe, so kndigte Manfred Kanzleiter an, werde die st?dtische Personalpolitik "ein zentrales Thema sein".	V	All	2006/9/22	*
7	2	styling data	Dieses Glasdach l?sst sich auf Knopfdruck hell oder dunkel schalten. Hell ist es fast v?llig durchsichtig und bietet auch bei kalter Witterung ein Open-Air-Erlebnis.	A	Rot	2006/9/22	*
Sheet2

I have try to use split macro to detect alt+enter in each column, but it is not work properly.

can some one help?

thanks a lot.

 

[Krishnakumar]

Hi,

Sub kTest()
    Dim ka, k(), i As Long, n As Long, c As Long
    Dim x, MT255Chars As String, s As String, j As Long
    Dim cAddr()   As String, m As Long, p As Long
    
    '// User settings
    Const SourceShtName         As String = "Sheet1"
    Const SourceRange           As String = "A:G"
    Const DestShtName           As String = "Sheet2"
    Const DestRange             As String = "A1"
    '//End
    
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, Range(CStr(SourceRange)))
    End With
    
    ReDim k(1 To UBound(ka, 1) * 3, 1 To UBound(ka, 2))
    
    For i = 1 To UBound(ka, 1)
        x = Split(NormalizeString(ka(i, 3)), Chr(10))
        For c = 3 To 6
            ka(i, c) = NormalizeString(ka(i, c))
        Next
        For m = 0 To UBound(x)
            n = n + 1
            For c = 1 To UBound(ka, 2)
                Select Case c
                    Case 3 To 6
                        On Error Resume Next
                        s = ""
                        s = Split(ka(i, c), Chr(10))(m)
                        On Error GoTo 0
                        If Len(s) > 255 Then
                            j = j + 1
                            ReDim Preserve cAddr(1 To j)
                            cAddr(j) = Cells(i, c).Address(0, 0)
                            MT255Chars = MT255Chars & "," & s
                        ElseIf Len(s) Then
                            k(n, c) = s
                        Else
                            k(n, c) = ka(i, c)
                        End If
                    Case Else
                        k(n, c) = ka(i, c)
                End Select
            Next
        Next
    Next
    If n Then
        With Worksheets(DestShtName).Range(CStr(DestRange))
            .Resize(n, UBound(k, 2)).Value = k
        End With
    End If
End Sub
Private Function NormalizeString(ByVal strText As String) As String
    Dim x, i  As Long, s As String
    
    x = Split(strText, Chr(10))
    For i = 0 To UBound(x)
        If Len(Trim$(x(i))) Then
            s = s & Chr(10) & Trim$(x(i))
        End If
    Next
    If Len(s) > 1 Then NormalizeString = Mid$(s, 2)
End Function

HTH

 

[lynn8566]

Hi Kris

Many thanks to you help, but it still not work.

it says "runtime error 9 Array Index Out Of Bounds!"

on the sheet 1 its cells is from A to I and I have tried to fix it, still not possible.

 

[Krishnakumar]

Hi,

Which line you get the error ?

And what's in Col F & G ?

 

[lynn8566]

Hi,

Which line you get the error ?

And what's in Col F & G ?

I have tried again, this time I copied this worksheet (which need to be processed) from origin documents to a blank excel files and it works brilliant thanks a lot.

in origin excel files it also have many macro and private func call maybe those makes errors.

 

[lynn8566]

another questions, commonly in each month there will be 10 to 20 of this meeting records.

Can I have just one blank excel files with this code, and copy all the request information into this new excel and sort them?

the request information is on worksheet1 and the name of document is follow the rules like "yyyy-mm-dd_C412_MV_minutes"

C412 is variable
MV means project, it also chance
minutes is name of worksheet1

 

[lynn8566]

Hi Krishnakumar

Sorry I have tried this code with the original documents, but it seems some problem appeared. column H was not split correctly, it still keep the shape as origin. and some of content in Column C was missing.

can you help. many thanks

 

[Krishnakumar]

Hi,

Can you post a screen shot of that particular rows (Col H) of data ?

 

[lynn8566]

here is the screenshot, you can see column C D E were split correctly, but Column H was not.

for topic 6, first 3 rows was split correct, the rest of rows may not have corresponding date, if it is not, can we just skip it, leave that column in blank.
Thank you

[IMG]http://farm6.static.flickr.com/5027/5669336262_14947e808d.jpg[/IMG]

 

[Krishnakumar]

Hi,

Can you show me the source data ? (before run the macro)