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

  • Thread starter Thread starter sal2
  • Start date Start date
S

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
 
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
 
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
 
Back
Top