how to select and append a column in VB and excel tia sal2

[sal2]

Greetings All

I'm trying to select a column of cells and append all there contents to
one cell. Does anyone have some example vb.script that does this in excel

Example
select excel column A1 to A70
Append all values in A1 to A70 in Cell C4 with the phrase "code" after
each value. so C4 would look like "tre345 code tre346 code tre347 code"


Please reply to newsgroups as email address isn't real
tia sal2

 

[Per Jessen]

Hi

Try this:

Public Function AppendCode(TargetRange As Range)
For Each cell In TargetRange
If myStr = "" Then
myStr = cell.Value & " code"
Else
myStr = myStr & " " & cell.Value & " code"
End If
Next
AppendCode = myStr
End Function


In C4 enter the formula:

=AppendCode(A1:A70)

Hopes this helps

 

[Mike H]

Hi,

Put this in a general module

Function AddSuffix(Rng As Range, suffix As String) As String
For Each c In Rng
If c.Value <> "" Then
MyString = MyString & c.Value & " " & suffix & " "
End If '
Next
AddSuffix = MyString
End Function


Call with this in C4

=addsuffix(A1:A70,"Code")

Mike