Combine 2 var into 1 string with & in between

[bumbum2812]

Hi Experts Please help me combine 2 var into 1 string with & in between.
My data is from A1:A2 & i have below code to put in MsgBox.

 Sub abc() 
Dim lr3   As Long 
Dim var As Variant 
lr3 = Range("A" & Rows.Count).End(xlUp).Row 
For Each var In Sheets("Sheet1").Range("A1:A" & lr3)     
MsgBox var 
Next var
 End Sub

Any help will be so much apricated

 

[bumbum2812]

Use

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 !

 

[Fluff]

Glad to help & thanks for the feedback.

 

[bumbum2812]

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]

How about

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

 

[bumbum2812]

How about

   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]

Is there anything in the array?

 

[bumbum2812]

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]

You need to keep it in the order I showed, other it will fail if the array only has one value.

 

[bumbum2812]

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]

Oops, missed the Not

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