View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default 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!