Need Help Looping through Sheet with Concatenation

Topic

New Reply

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 jgraves0000@gmail.com around the email so the result looks as follows in

Column F jgraves0000@gmail.com[/email]

Also, cell phone in:

Column E 401-754-8797

And concatenate Text around the cell so the result looks as follows in

Column G Text

Any help will be appreciated

Reply | Quote

Replies

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 = “” & Cells(I, 4).Value & “”
    Cells(I, 7).Value = “Text”
Next I
End Sub

35032-Contacts

HTH,
Maud

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Thanks again for the tutorial

Reply | Quote