Concatenating Loop

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Pierce - 02 Oct 2008 02:39 GMT

I need to make the following code work for a variable number of rows
and stop when it reaches a #VALUE! error.

Public Sub ConcatenateEmail()
Dim myString As String
Dim r As Long

myString = ""

For r = 4 To 585
myString = myString & Cells(r, "G") & ","
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString
End Sub

Reply to this Message

RyanH - 02 Oct 2008 03:35 GMT

I think this is what you are looking for. The loop will run from row 4 to
last row in Col. G.

Option Explicit

Public Sub ConcatenateEmail()

Dim myString As String
Dim r As Long

For r = 4 To Cells(Rows.Count, "G").End(xlUp).Row
If IsError(Cells(r, "G")) Then
Exit For
Else
myString = myString & Cells(r, "G").Value & ","
End If
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString

End Sub

Signature

Cheers,
Ryan

> I need to make the following code work for a variable number of rows
> and stop when it reaches a #VALUE! error.
[quoted text clipped - 12 lines]
> Range("A2") = myString
> End Sub

Reply to this Message

John Pierce - 11 Oct 2008 16:14 GMT

New, but similar, problem. I would like to put the cell contents of
Col F, from Row 4 to the first Error (#VALUE!) into an array and then
write the array into Col A of another sheet. Capeesh?

Reply to this Message

RyanH - 02 Oct 2008 03:36 GMT

This code will loop from row 4 to last row in Col. G

Option Explicit

Public Sub ConcatenateEmail()

Dim myString As String
Dim r As Long

For r = 4 To Cells(Rows.Count, "G").End(xlUp).Row
If IsError(Cells(r, "G")) Then
Exit For
Else
myString = myString & Cells(r, "G").Value & ","
End If
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString

End Sub

Hope this helps! If so please let me know by clicking "YES" below.

Signature

Cheers,
Ryan

> I need to make the following code work for a variable number of rows
> and stop when it reaches a #VALUE! error.
[quoted text clipped - 12 lines]
> Range("A2") = myString
> End Sub

Reply to this Message

RyanH - 11 Oct 2008 16:38 GMT

You don't have to fill an array to accomplish what you need. I added one
line to the code I gave you earlier which will do what your second question
is asking. You may need to adjust the sheet name. I assumed that the sheet
you are copying too has a header.

Option Explicit

Public Sub ConcatenateEmail()

Dim myString As String
Dim r As Long

For r = 4 To Cells(Rows.Count, "G").End(xlUp).Row
If IsError(Cells(r, "G")) Then
Range("F4:F" & r).Value = Sheets("Sheet2").Range("A2:A" &
r).Value
Exit For
Else
myString = myString & Cells(r, "G").Value & ","
End If
Next r

myString = Left(myString, Len(myString) - 1)
Range("A2") = myString

End Sub

Hope this helps! If so, then click "YES" below. Thanks!

Signature

Cheers,
Ryan

> This code will loop from row 4 to last row in Col. G
>
[quoted text clipped - 36 lines]
> > Range("A2") = myString
> > End Sub

Reply to this Message

John Pierce - 11 Oct 2008 17:13 GMT

Thanks, Ryan, but as I said the problem has changed. I don't want to
concatenate the cell contents into a single string. I need them listed
on the other sheet in a column. The difference is that on sheet 1, col
F is formulas and on Sheet 2, col A, I want to 'paste special' the
values for further work. For that, I think capturing it all in an
array and then writing it all at once be most efficient.

Reply to this Message

RyanH - 11 Oct 2008 18:16 GMT

I am confused on what you are needing. Please explain in detail and give an
example of what you are wanting.

Signature

Cheers,
Ryan

> Thanks, Ryan, but as I said the problem has changed. I don't want to
> concatenate the cell contents into a single string. I need them listed
> on the other sheet in a column. The difference is that on sheet 1, col
> F is formulas and on Sheet 2, col A, I want to 'paste special' the
> values for further work. For that, I think capturing it all in an
> array and then writing it all at once be most efficient.

Reply to this Message

John Pierce - 11 Oct 2008 19:41 GMT

The project is this:
1. Import a file consisting of one continuous string of hundreds of e-
mail addresses.
2. Parse the string into one address per line and clean out junk
3. Eliminate duplicates
4. Fix broken addresses: missing “@” or “.”
5. Reconcatenate for reuse.
You helped me get from step 2 to step 5, but I had neglected steps 3
and 4. So now I want to have the parsed addresses, (which are on
Sheet1, Col F, in the form of formulas) copied to another sheet as
values (text) so I can edit them.

Reply to this Message