Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help Looping through Sheet with Concatenation

    Need Help Looping through Sheet with Concatenation

    Excel 2007

    I have a 1000 item Contact List that contains name, email, cell, etc

    I need a macro that loops through the sheet and will take the Email in

    Column D jgraves0000@gmail.com

    And concatenate <a href="mailto: & ">jgraves0000@gmail.com</a> around the email so the result looks as follows in

    Column F <a href="mailto: jgraves0000@gmail.com">jgraves0000@gmail.com</a>

    Also, cell phone in:

    Column E 401-754-8797

    And concatenate <a href="sms: & ">Text</a> around the cell so the result looks as follows in

    Column G <a href="sms: 401-754-8797">Text</a>

    Any help will be appreciated
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    John,

    Here is some code that will do what you had requested:

    Code:
    Public Sub Mailto()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 3 To LastRow
        Cells(I, 6).Value = "<a href=" & Chr(34) & "mailto: " & Cells(I, 4).Value & Chr(34) & ">" & Cells(I, 4).Value & "</a>"
        Cells(I, 7).Value = "<a href=" & Chr(34) & "sms: " & Cells(I, 5).Value & Chr(34) & ">Text</a>"
    Next I
    End Sub
    Contacts.png

    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow! very nice, could you translate this, 2nd grade version?

    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    When I started with Access there was Rogers Access Library, Allen Brown & FMS that have sample database's to demonstrate the capabilities of Access, are there sites like that that demonstrate the capabilities of Excel?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Sure, John.

    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    To find the last row, go to the last possible row of a worksheet and work up looking for any data in the cells in column 1. When the first instance of data is found, that is the last row. Once the last row becomes known, we can loop through each row, perform an action, and then move to the next row and repeat the action, and so on, and so on, until the last row has been completed signaling a stop. In this case the actions are to construct data for cells in columns 6 and 7 using data from cells in columns 4 and 5. We are looping from rows 3 to 8. The advantage of finding the last row is that as we add more rows, the range of rows looped also increases because the last row is the stopping point. So the code becomes dynamic as it will work on as many rows as we add.

    There are an overabundance of sites that boast the power of Excel. Googling something so basic as "Excel for beginners" opens doors for tricks, tips, and advanced techniques.

    Maud

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again for the tutorial

Tags for this Thread

Posting Permissions

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