Macro to edit text string data input

[Dee]

I am attempting to write a macro that will edit subsequent rows of data (text
strings always in the same format for each row) which I paste in from
another source one group at a time, and place the portions I wish to retain
elsewhere in my spreadsheet in the form of a mailing list. In other words, I
want to: #1 copy text string data from another source (which is always laid
out in the same format for each record); #2 paste the data into my
spreadsheet; #3 run a macro which pulls out only those portions I need to
retain and places those portions elsewhere in my spreadsheet one record after
another; and #4: start the process over with the next group of data pasted in
on top of the first group.

I have two problems: 1) While I have written a macro that successfully
edits the first group of pasted data text strings, when I pull in a
subsequent set of data strings and paste them over the first set of data and
then run the macro, the results returned are always those results obtained
from the very first set of data strings. 2) I am not clear on how best to
copy the parsed -out portions of each record to the mailing list portion of
my spreadsheet -- that is, I do not know how to append the mailing list
portion of my spreadsheet with each new parsed-out record.

What I am trying to avoid is having to pull every record in from my source
at one time and THEN run the macro to edit out the portions of each record I
wish to retain. I want to do it one record at a time--paste in one record
group, edit down and copy to elsewhere what I need to retain from the record
utilizing a macro, go back to my source, copy the next record group and paste
it into my spreadsheet over the first record group, run the editing and copy
macro(s), go back to the source for the next group, etc.

The source copy and subsequent pasting into Excel results in the data ending
up in cells A1, B1. C1, etc. with alot of extraneous text and spaces I don't
need. After the paste, it looks like this:

A1: John D Doe Company Title
B1: See descriptions... Other Titles
C1: See descriptions... (e-mail address removed) CompanyABC Inc.
D1: Webpage Address123 Anystreet, Suite 2
E1: Anytown, USA 12345 Phone(123)456-7890 Ext. 0 Fax(123)456-7980

Can anyone help me please?

Thanks!

 

[K Dales]

Can you show your existing macro? Hard to answer without seeing it. Also
what does the input text look like? How do you want the output to look?

 

[Dee]

I've solved the first problem (i.e. the reduplication of the first record's
content over and over again.).

Now I just need to figure out how to append the output list each time I pull
a record in and parse it down to what I need to retain.

My parsed output is all text strings, contained in different A cells where
I've embedded the formulas necessary to parse out the info.

For instance, in cell A38 I have the last name from the record: Doe
In cell A14 I have the email parsed from the record: (e-mail address removed)
In cell A18 I have Office Address
In cell A25 I have the office phone: (123) 456-7890
In cell A28 I have the first name: John
and so on down the A column

The output needs to be arrayed in a table across columns headed as follows:

A40: Last Name
B40: First Name
C40: Office
D40: Company Address
E40: City and Zip
F40: Phone
G40: Fax
H40: Email address

with the first record's information appearing starting in A41....H41.

In the Excel macro I recorded I attempted to follow Lotus 123 macro language
where you can designate range names and then direct the cursor to the first
blank row in a list by {GOTO} rangename and then {END}{DOWN} {DOWN} type
commands. However, my Excel macro does not take me to first blank line at the
end of my record list like a 123 {END}{DOWN}{DOWN} would...instead it keeps
taking me to the literal cell that got recorded when I recorded the
keystrokes.

Here's what I recorded:

Sub copy()
'
' copy Macro
' Macro recorded 11/28/2005 by McHenry
'
' Keyboard Shortcut: Ctrl+c
'
Application.CutCopyMode = False
Range("A14").Select
Selection.copy
Application.Goto Reference:="email_address"
Selection.End(xlDown).Select
Range("H42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A18").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Company_Address"
Selection.End(xlDown).Select
Range("D42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A19").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Office"
Selection.End(xlDown).Select
Range("C42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A21").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="City_and_Zip"
Selection.End(xlDown).Select
Range("E42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A24").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="fax"
Selection.End(xlDown).Select
Range("G42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A25").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="phone"
Selection.End(xlDown).Select
Range("F42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A28").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="First_Name"
Selection.End(xlDown).Select
Range("B42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A35").Select
Application.CutCopyMode = False
Selection.copy
Application.Goto Reference:="Last_Name"
Selection.End(xlDown).Select
Range("A42").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
End Sub


Thanks K Dales!

 

[Tom Ogilvy]

to find the next open row in you data (assume the sheet with the cleaned up
data is named Data)

set rng = Worksheets("Data").Cells(rows.count,"A").End(xlup)(2)
rng.Value = sLastName
rng.offset(0,1).value = sFirstName
rng.offset(0,2).Value = sOffice

and so forth

 

[Dee]

Tom:

I set up Sheet 1 as my 'data input' and 'parse data' sheet. I set up Sheet 2
as the 'clean data' sheet and named it Data, with my mailing list headers in
A1 through H1.

I added the following macro to determine the next open row in the Data sheet
per your instructions:

Sub Append()

Set rng = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp)(2)
rng.Value = sLast_Name
rng.Offset(0, 1).Value = sFirst_Name
rng.Offset(0, 2).Value = sOffice
rng.Offset(0, 3).Value = sCompany_Address
rng.Offset(0, 4).Value = sCity_and_Zip
rng.Offset(0, 5).Value = sphone
rng.Offset(0, 6).Value = sFax
rng.Offset(0, 7).Value = sEmail_Address

End Sub

and I get the following error message when I run Append:

"Set" is highlighted

Microsoft Visual Basic error
Compile Error:
Invalid Outside Procedure

What am I doing wrong?

Thanks...

Dee

 

[Tom Ogilvy]

I copied you code from the email, pasted it into a module. Renamed a sheet
to be named Data and it ran fine for me.


Perhaps you have pasted it inside another procedure or something or picked
up a stray End Sub command after the procedure declaration line.