please help

[johnsjg]

I have several thousand addresses in excel that I need to import int
another program for mailing purposes.

The addresses are seperated into 7 lines each going down the A column.


All contain: Name, Occupation, Address, (City,State ZIP)
Some also contain: 2nd Address, email address.

The problem is that due to the varied lengths of the address th
information will not appear in the same spots when imported into th
mailing program.

I am trying to write a macro that will seperate these addresses

With:

Line1 of Addresses in Column B
Line2 of Addresses in Column C
Line3 of Addresses in Column D
Line4 of Addresses in Column E
Line5 of Addresses in Column F
Line6 of Addresses in Column H
Line7 of Addresses in Column I

How can I do this? I'm going insane.

If I'm asking too much then please let me know. That way I'll kno
that this is way over my head and I'll try to figure something els
out.

Thank

 

[Doug Robbins - Word MVP]

Maybe you should be asking this in an Excel group, but if each a record
occupies 7 cells regardless of whether some cells are empty of not, so that
the first item in the first record in in cell R1C1 and the first item in the
second record is in cell R8C1 etc, and if you select all of the data, copy
it to the clipboard and then paste it into a Word document, so that you have
in that document a single column table with the first data item in the first
cell (that is no header row) and you run a macro containing the following
code, it will add a 7-column table to the document and insert the data items
for each record into the respective cells of each row of that table. If you
then at a header row, it can be used as a datasource for a mailmerge:

Dim source As Table, target As Table, myrange As Range, i As Long, m As
Long, n As Long
ActiveDocument.Range.InsertAfter vbCr
Set myrange = ActiveDocument.Range
myrange.Collapse wdCollapseEnd
Set source = ActiveDocument.Tables(1)
Set target = ActiveDocument.Tables.Add(Range:=myrange,
Numrows:=source.rows.Count / 7, NumColumns:=7)
For i = 0 To source.rows.Count - 1
Set myrange = source.Cell(i + 1, 1).Range
myrange.End = myrange.End - 1
target.Cell(Int(i / 7) + 1, i Mod 7 + 1).Range = myrange
Next i


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[johnsjg]

anybody

 

[johnsjg]

anybody

 

[johnsjg]

anybody

 

[Doug Robbins - Word MVP]

Your question was answered yesterday.

Ask IRubin to fix up his crappy WordForums.com website so that it doesn't
cause duplicates and triplicates of the messages posted to his site to
appear on the servers that actually host these newsgroups. For you own
part, I would suggest that you do not access the newsgroups via
WordForums.com. If you can, use a newsreader such as Outlook Express. If
you cannot use that, then connect to the newsgroups via
http://communities.microsoft.com/newsgroups/default.asp Your posts will
appear more promptly and answers will also be available as soon as they are
posted, rather than when IRubin gets around to it.


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP