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.

 

[lynn8566]

yes, i have already changed this number, still not work correctly.

 

[Krishnakumar]

Hi

Can you post the screen shot of the data (couple of more rows) ?

 

[lynn8566]

please try with this, sorry i have to change the content in column C, but the format is as origin.

in column D type I meaning it is a information and there will no date records in column H corresponding to it.for type A mostly have date, but sometimes date was missing or sometime it is "ongoing", for type V no date records. another problem was that, I, A and V sometime appear crossed, there is no common discipline.

the challenge is if you filled column H with NA or * mark in those place where date was missing, your code will be run perfect. but this excel file will goes to over 4000 rows before split, so it just no possible to input by hands.

anyway i sincerely thank for all work you have done.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	TOP	Topic	Meeting minutes	Type	responsible/contact	Teilnehmer	Ampel	until	attachments & links
2	*	*	*	*	*	*	*	*	*
3	2006-09-24	2nd meeting	xxxxx	*	*	*	*	*	*
4	1	requirement sections	US space agency managers said the ship would not now lift off before 8 May.US space agency managers said the ship would not now lift off before 8 May.US space agency managers said the ship would not now lift off before 8 May.US space agency managers said the ship would not now lift off before 8 May.US space agency managers said the ship would not now lift off before 8 May.	AV	OrtalAll	*	*	2006-09-22	requirement sections
5	2	smaragd access to styling data	It should have left Earth on Friday but has been held on the ground because of an electrical failure in a switchbox connected to a hydraulics power unit.The youngest of America's reusable spaceplanes is set to deliver a $2bn (?1.2bn) particle physics experiment to the International Space Station (ISS).	AA	model verifiersGun	*	*	2006-09-222006-09-20	*
6	3	feed back	On Sunday, Nasa announced the repairs would be lengthy but could not state precisely how long it would take to get the orbiter in a position to make another launch attempt. "We can tell you pretty much that it's not going to be any earlier than the 8th," explained Mike Moses, the chair of the mission management team."That doesn't mean we're going to go launch on the 8th; that just means we know the 8th is our next available opening."So we'll kinda start looking at that but we're not ready to set the schedules today. There's still a whole lot of short-term work that has to be done."1. Friday's glitch started with an errant heater in one of the three auxiliary power units (APUs) in Endeavour - all of which must be in perfect working order for a launch to be allowed to proceed.The APUs provide hydraulic power to steer the vehicle during ascent and entry. The heater's job is to prevent hydrazine fuel in a feed pipe to a unit from freezing.	IAAAAA	RealSomalSomalSomalSomalReal	*	*	2006-09-292006-09-292006-09-292006-09-292006-09-22	*
7	4	status	The families of the crew were also told there was nothing to keep them at Kennedy.	A	Ciop	*	*	2006-09-22	*
8	5	miscellaneous	1. The families of the crew were also told there was nothing to keep them at Kennedy.2. The extra delay means Endeavour's six astronauts have returned to their base at Nasa's Johnson Space Center in Houston, Texas.	AA	WiggroWiggro	*	*	2006-09-252006-09-29	*
9	TOP	Topic	Meeting minutes	Type	responsible/contact	Teilnehmer	Ampel	until	attachments & links
10	*	*	*	*	*	*	*	*	*
11	2006-09-25	3rd meeting	xxxx	*	*	*	*	*	*
12	1	Smaragd access to styling data	? Managers need then to stage Endeavour's ascent so that the shuttle's movements in orbit do not conflict with a Russian Soyuz vehicle that is supposed to be leaving the space station later this month.The complex task of switchbox replacement and testing means Endeavour's next opportunity to launch has to wait until after the Atlas has had a chance to get into space.	IA	Realmodel verifiers	*	*	2006-10-06	MCG_Scan_Data_Smaragd
13	2	Storage of styling data	? Complicating matters is the launch of an Atlas rocket on the nearby Cape Canaveral Air Force Station scheduled for 6 May."There's a heck of a lot of work ahead of the team," said launch director Mike Leinbach. "I'm extremely proud of the work we've already done over the weekend to understand this problem, and now that we've essentially pinpointed the problem to inside of the load control assembly we'll get on with the business of taking that guy out and replacing it with the new one." ? FeedbackNasa engineers were not sure at first whether the heater's reluctance to operate was a simple thermostat failure or a more deep-seated issue in an electrical switchbox.	IIAA	RealRealRealRavens	*	*	2006-10-062006-10-06	*
14	3	Update of styling models	Complicating matters is the launch of an Atlas rocket on the nearby Cape Canaveral Air Force Station scheduled for 6 May.	I	Held	*	*	*	*
15	4	Feed back	The extra delay means Endeavour's six astronauts have returned to their base at Nasa's Johnson Space Center in Houston, Texas.The families of the crew were also told there was nothing to keep them at Kennedy.This included the shuttle commander Mark Kelly's wife - Arizona congresswoman Gabrielle Giffords. She is recovering from a gunshot wound to the head, but was allowed by her doctors to travel to Florida's Space Coast to see her husband's lift-off. She has now returned to the hospital, also in Texas, where she is undergoing rehabilitation.Friday's glitch started with an errant heater in one of the three auxiliary power units (APUs) in Endeavour - all of which must be in perfect working order for a launch to be allowed to proceed.	IAIA	WarterTrummStri?kHeld, Reeve	*	*	2006-10-062006-10-06	*
Sheet1

 

[lynn8566]

Hi
there is some progress, i have changed following code´

                Select Case c
                    Case 3 To 8
                        On Error Resume Next
                        s = ""
                        s = Split(ka(i, c), Chr(10))(m)

to

                Select Case c
                    Case 3 To 8
                        On Error Resume Next
                        s = " "
                        s = Split(ka(i, c), Chr(10))(m)

i have added a space in the "" mark, then it split nearly correct. but still it cannot identify the sequence of date in column H, but it can identify blank.

i think this will be difficult to correct with code right? but the date is very important evidence in the following analytic work, so is there any solution for this?

 

[Krishnakumar]

No luck so far. Will look into in detail this weekend .

 

[lynn8566]

Hi Krishnakumar

have any solution for time column?

 

[Krishnakumar]

Hi,

Unfortunately no