VBA to move text strings to proper Column

[Researcher007]

Hi-
I am hoping some one could help me.

I have Genealogy data in an excel file in one column like this.

Alan Hageman
Birth: 28 Aug 1953 in Evanston, Cook County, IL
Marriage: 03 Mar 1979 in DeKalb, DeKalb County, IL
Alan Koch
Birth: Unknown
Marriage: Unknown
Albert August Haseman
Birth: 03 Nov 1870 in Park Ridge, Cook County, IL
Marriage: 1895
Death: 03 Feb 1944 in Arlington Heights, Cook County, Illinois, USA
Cemetery: 30 Jan 2004 in Maine Township Cemetery
Burial: 07 Feb 1944 in Park Ridge, Cook County, IL
Burial Plot: 17 Dec 2002 in Burial plot information: Block 122
Albert Carl Friedrick Franz Maas
Birth: 29 Oct 1887

I would like the data to format like this in 7 seperate columns.
Name Birth Death Marriage Burial Cemetery Plot
Name 1 Birth1 Death 1 Marriage 1 Burial 1 Cemetery 1 Plot 1
Name 2 Birth 2
Name 3 Birth 3 Death 3


I have 8000 lines, and the Birth:, Death:, Marriage:, etc, designator always precedes the data.

Is this possible or am I doomed by copy/pasting or dragging and dropping to the appropriate column?

[walruseggman]

Please Login or Register  to view this content.

[Researcher007]

Thank you for your post.

I copied your script to the VB under Modules 1

I run the script and get an error at InfoVal = Split(Cell.Value, ": ")(1)

This is my edited script, did i miss a variable? The error is Run-time Error 9

Please Login or Register  to view this content.

[walruseggman]

Hard to say without seeing your workbook. But if you're getting an error on that line, it seems to suggest you have a cell that has a colon and space with nothing after it, like "Example: ". I say that because if the previous line ran w/o error, there would have to be a colon and space in the cell.

[Researcher007]

Your Suggestion was correct. I found a blank value after a colon. in fact I corrected that one and found more. Once I cleaned the blank data-set out of the list it ran flawlessly. Ironically, those blanks was why I wanted to create this report in the first place.

Your help was very much appreciated. You saved me hours of work, which I can now focus on actual research time.

[Researcher007]

Your Suggestion was correct. I found a blank value after a colon. in fact I corrected that one and found more. Once I cleaned the blank data-set out of the list it ran flawlessly. Ironically, those blanks was why I wanted to create this report in the first place.

Your help was very much appreciated. You saved me hours of work, which I can now focus on actual research time.

[walruseggman]

Glad to help. We could account for those blank values in the code if you want, but i'm guessing you really only needed to run this code once anyway.

Please mark this thread as SOLVED, and any reputation (the * in the lower left of my posts) would be much appreciated.

[Researcher007]

I will probably re-run this with a new list sometime in the future once I begin to fill in blanks. Having that issue is actually a good thing. It allows me to clean up missing data, now that I know what the error means.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.