Macro to edit text string data input
 

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... 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!

Macro to edit text string data input
 

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?
--
- K Dales


"Dee" wrote:

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... 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!

Macro to edit text string data input
 

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:
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!


"K Dales" wrote:

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?
--
- K Dales


"Dee" wrote:

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... 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!

Macro to edit text string data input
 

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

--
Regards,
Tom Ogilvy


"Dee" wrote in message
...

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:
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!


"K Dales" wrote:

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?
--
- K Dales


"Dee" wrote:

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... 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!

Macro to edit text string data input
 

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" wrote:

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

--
Regards,
Tom Ogilvy

Macro to edit text string data input
 

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.


--
Regards,
Tom Ogilvy


"Dee" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy