Worksheetfunction does not have concatenate()

[lezawang]

Hi
I thought class WorksheetFunction has all excel functions inside but I just checked and noticed that Concatenate() function is not there.

Where to find it? Is there any object in vba which All excel functions?

Thank you for all your help.

 

[sheetspread]

& will concatenate on both the worksheet and in VBA:

Excel 2010
	A	B	C	D	E
1	Hello	3	Twelve	Hello 3 Twelve	Hello 3 Twelve
Sheet3

Cell Formulas
Range	Formula
D1	=A1&" "&B1&" "&C1

Sub concatenatex()
[e1] = [a1] & " " & [b1] & " " & [c1]
End Sub

 

[lezawang]

Wow, amazing idea and coding. wow. I just wanted to do it under loop for 10 cells but got error message. Is that possible to put it in a loop to do 10 cells at same time?
Thank you so much.

Sub concat()
Dim i As Integer
For i = 1 To 10
[c] = [a] & " " & [b]
Next i
End Sub

 

[sheetspread]

Try:

Excel 2010
	A
1	q
2	w
3	e
4	r
5	t
6	y
7	u
8	i
9	o
10	p
11
12	q w e r t y u i o p
Sheet4

Sub concat()
Dim i As Integer, x As String
x = ""
For i = 1 To 10
x = x & " " & Cells(i, 1).Value
Next
Range("a12") = x
End Sub