Combine 2 var into 1 string with & in between

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
20
Office Version
365
Platform
Windows
Use
VBA Code:
If IsArray(Ary) Then .Subject ="Hello -"& Join(Ary, "&") Else .Subject= "Hello -" & Ary
Thank you so much, this will work if i move the code inside my sub to send email.
Good day to you Sir !
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback.
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
20
Office Version
365
Platform
Windows
Hi Fluff,

Can you help to write a code like this in VBA & able to put in .subject for outlook & it can show apply for single Ary as well.

=IFS(LEN(Join(Ary, "&"))>200,"Multiple Jobs",LEN(Join(Ary, "&"))<200,Join(Ary, "&")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
How about
VBA Code:
   If IsArray(Ary) Then
      txt = Ary
   ElseIf Len(Join(Ary, "&")) > 200 Then
      txt = "Multiple jobs"
   Else
      txt = Join(Ary, "&")
   End If
   .Subject = txt
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
20
Office Version
365
Platform
Windows
How about
VBA Code:
   If IsArray(Ary) Then
      txt = Ary
   ElseIf Len(Join(Ary, "&")) > 200 Then
      txt = "Multiple jobs"
   Else
      txt = Join(Ary, "&")
   End If
   .Subject = txt
Hi, i replace this with yours but it returns blank.
If IsArray(Ary) Then .Subject ="Hello -"& Join(Ary, "&") Else .Subject= "Hello -" & Ary
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
Is there anything in the array?
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
20
Office Version
365
Platform
Windows
Is there anything in the array?
Hi,yes it has data in the array, i have tried to modify as below, it's work for > & < condition but dont work for only Ary. Can you have a look please.

If Len(Join(Ary, "&")) > 200 Then
.Subject = "Multiple jobs"
ElseIf Len(Join(Ary, "&")) < 200 Then
.Subject = " " & Join(Ary, " & ")
ElseIf IsArray(Ary) Then
.Subject = Ary
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
You need to keep it in the order I showed, other it will fail if the array only has one value.
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
20
Office Version
365
Platform
Windows
You need to keep it in the order I showed, other it will fail if the array only has one value.
I keep it in order like below, but it return blank

With Sheets("Re-Open")
Ary = Application.Transpose(.Range("A5", .Range("A" & Rows.Count).End(xlUp)).Value)
End With

If IsArray(Ary) Then
txt = Ary

ElseIf Len(Join(Ary, "&")) > 200 Then
txt = "Multiple jobs"
Else
txt = Join(Ary, "&")
End If
.Subject = txt
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
Oops, missed the Not
VBA Code:
   If Not IsArray(Ary) Then
      txt = Ary
   ElseIf Len(Join(Ary, "&")) > 200 Then
      txt = "Multiple jobs"
   Else
      txt = Join(Ary, "&")
   End If
   .Subject = txt
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,856
Messages
5,507,748
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top