Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: rearranging copied addresses into columns

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Chicagoland
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First posting; glad I found this.

    If I copy a long list of addresses (say, from the Web) arranged in address blocks, like this:

    Jack Nicholson
    Big Bank Corp.
    123 Fourth Street
    Second Floor
    Chicago, IL 60603

    and want to end up with ALL these addresses in columns (all names in Col A, Company in Col B., etc.), how can I do this? To complicate things, some addresses have more rows in the address block than others; I can work that out by sorting the resulting columns and moving the data, unless someone has something very clever in mind.

    Alternatively, does anyone know of a commercial product, faily cheap, that will convert addresses into ACT! contact management software format?

    thanks!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I had to do the very same thing myself with an output from ACT about a week ago.

    The following worked.
    Before I started all the addresses were in column A and started in cell A1.
    There was also a blank line in between each address and, as in your case, the addresses took up variable numbers of lines.


    --
    Dim myRow As Integer
    Dim myIncrement As Integer


    myRow = 2
    myIncrement = 1
    Range("A1").Select

    While ActiveCell.Value <> ""

    ActiveCell.Offset(1, 0).Cut
    ActiveCell.Offset(0, myIncrement).Select
    ActiveSheet.Paste
    Rows(myRow & ":" & myRow).Select
    Selection.Delete Shift:=xlUp
    myIncrement = myIncrement + 1
    ActiveCell.Offset(-1, 0).Select
    If ActiveCell.Offset(1, 0).Value = "" And ActiveCell.Offset(2, 0).Value <> "" Then
    Rows(myRow & ":" & myRow).Select
    Selection.Delete Shift:=xlUp
    myIncrement = 1
    myRow = myRow + 1

    End If

    Wend
    --
    As with most of my code, it's probably a bit cumbersome, but seems to do the trick.

    Rgds
    AJ




  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Chicagoland
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    wow.

    I am quite new to macros, so I don't have any idea how to proceed, but I'm sure I can find someone around here who can take your code and make it do its thing on my lists.

    Thank you, VERY much, for taking the time. I really appreciate it.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Chicagoland
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, I thought it would be easy to find out how to convert your text into something that would actually do something, but no.

    Would you be so kind as to reply with something very fundamental, like:

    Start with the spreadsheet of your listed addresses opened. Click on "Tools/Macros..." and click on "New Macro" ....'

    Your response was like that of a native speaker of a foreign language who replied with a flood to the only phrase I've learned in his language. Thanks very much!!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello again!

    Sorry I've only just noticed your final post to this thread. I don't usually go that far back. (Was just having a final browse before I go for the day).

    Anyway, the simple guide to get this to work is as follows:

    1. Make a copy of your file incase this totally screws it up!

    2. Because I've just given you code above, you need to go directly to the Visual Basic Editor (under Tools>Macros).

    3. When you get there, in the project explorer window (usually the box filling the top half of the left side of the screen), right click on your filename and Insert Module

    4. At the top of the module type
    Sub YourMacroName ()
    You'll see this adds End Sub a couple of lines down. Then paste the macro above in between the Sub line and the End Sub line.

    5. Back in Excel see if it works...
    Tools>Macro>Macros pick YourMacroName and click run.

    If it works we're laughing! If it brings up an error message, reply telling me what it is. If it runs all the way through and does not work as you intended, go back to the Visual Basic Editor. Shrink the window a bit so you can see what's going on in Excel at the same time. Put your cursor anywhere in the macro and hit F8 repeatedly to step through it line by line. This will give you a clue as to where it's falling down. Again, reply and me or someone else will fix it!

    Rgds
    AJ



  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As to ACT.
    ACT will import a text delimited file.
    The key to success with the import is to ensure that your column headers are identical to the field names in ACT.
    You should split your name column into first name and last name columns. Act will merge these two columns into the contact field automatically.

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Chicagoland
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ: you are the best; thanks very much for taking the time to educate a frustrated banker.

    Let me know if I can do anything for youl.

  8. #8
    New Member
    Join Date
    Sep 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks guys.
    ASAP, I downloaded it, it's not working for this. Seems like it pretty much just does the normal transpose function, correct?

    Tom, I tried several times your idea. I don't know why, but it doesn't work. I failed to mention that I only listed 4 records as an example, I really have 500 records to do this to.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •