How to use TEXTJOIN in vba

[capson]

Hello,

I am trying to use TEXTOIN in vba

Sub AAA()
Dim str As String


    str = WorksheetFunction.TextJoin(" ", False, "E2:E6")
    MsgBox str


End Sub

This gives "E2:E6" as the return

If I try this instead:
str = WorksheetFunction.TextJoin(" ", False, E2:E6)

I get error

Is there a way to use TEXTJOIN in vba?

Thanks

 

[Rick Rothstein]

I am trying to use TEXTOIN in vba

Sub AAA()
Dim str As String

    str = WorksheetFunction.TextJoin(" ", False, "E2:E6")
    MsgBox str

End Sub

This gives "E2:E6" as the return

If I try this instead:
str = WorksheetFunction.TextJoin(" ", False, E2:E6)

I get error

Is there a way to use TEXTJOIN in vba?

I don't have a version of Excel that has TEXTJOIN in it, so this is just a guess. If the WorksheetFunction does, in fact, have a TextJoin member, I would think that third argument should be an actual range and not a text string address. Try the line of code this way and see if it works...

str = WorksheetFunction.TextJoin(" ", False, Range("E2:E6"))

As an aside, str is not a good name for a variable as that is the name of a built-in VB function.

 

[Dryver14]

If that doesnt work Range(...)

then try seperating them out, ie:str = WorksheetFunction.TextJoin(" ", False,E2,E3,E4,E5,E6)

 

[capson]

Thanks Rick, that worked also for the str comment

 

[Sakshine]

Hello all,

I have the TEXTJOIN formula with IF condition.
=TEXTJOIN(",",TRUE,IF(B4:I4="A",B$:I3,""))

A-SHIFT A	B-SHIFT B	MONTH-JAN
EMPLOYEE	1	2	3	4	5	6	7	8	DATES OF SHIFT A
EMP01	A	A	B	A	A	B	B	B	1,2,4,5
EMP02	B	B	A	A	B	B	B	A
EMP03	A	A	A	A	B	B	B	B

Can anyone help me to use this formula in vba (worksheet Function)?
(I don't want to use loop for this, using only worksheet function)

 

[RoryA]

You'd have to use Evaluate for that:

Dim v
v = Activesheet.Evaluate("TEXTJOIN("","",TRUE,IF(B4:I4=""A"",B$:I3,""""))")

for example.

 

[Sakshine]

I

You'd have to use Evaluate for that:

Dim v
v = Activesheet.Evaluate("TEXTJOIN("","",TRUE,IF(B4:I4=""A"",B$:I3,""""))")

for example.

I tried this but it's showing #VALUE!

 

[RoryA]

I didn't notice the error in your original formula. The B$ part at the end is missing a row number (I'd guess you meant B4)

 

[Sakshine]

It

You'd have to use Evaluate for that:

Dim v
v = Activesheet.Evaluate("TEXTJOIN("","",TRUE,IF(B4:I4=""A"",B$:I3,""""))")

for example.

It's perfectly working now.
Can you help me with getting autofill this formula using evaluate? It's autofilling only cell value and not formula.

 

[RoryA]

You can't autofill it precisely because it is not a formula. If you want to autofill, you should just put the formula in the cells. Why do you want to use VBA?