Append multiple excel cells into one string

[laurenbattaglia]

I need to append the following cells into a single string
adding a single quote before and after each number and a comma separating them. I also need this to occur until it reaches the last row of data.

Row Col A
1 603339978
2 603339979
3 603339980

What I need in a single string is:

'603339978','603339979','603339980',

Thanks for your help, Lauren

 

[VBAjedi]

Do you have a specific question regarding code you are developing, or are you hoping someone will code it for you?

I would probably just use a simple loop, along the lines of (not tested):

For x = 1 to 60000
  a = Worksheets("Sheet1").Range("A" & x).Value
  If Len(a) > 0 Then
    MyString = MyString & "'" & a & "',"
  Else
    Exit For
  End If
Next x

That will run until it encounters an empty cell, at which point it will exit the loop and you will have your string in the MyString variable.

Post back if that works for you. If not, describe your question more specifically and list the code you have so far.


VBAjedi

 

[PHV]

Something like this ?
Dim c As Range, s As String
For Each c In Range("A1").CurrentRegion
s = s & ",'" & c.Text & "'"
Next c
s = Mid(s, 2)
MsgBox s

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBAjedi]

PH

Your approach, while 3 lines shorter, assumes that Column B is empty. Even though CurrentRegion is convenient, I've found that it's generally better to take a few extra lines to actually verify that you are working with the desired range (although I'm not claiming that my brief code snippet is the best way to do that). I've seen Excel be wrong too many times!

Onward and upward!


VBAjedi

 

[PHV]

Good catch VBAjedi

 

[laurenbattaglia]

Thanks...That last one worked great!