Combine 2 var into 1 string with & in between

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
22
Office Version
365
Platform
Windows
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.

Rich (BB code):
 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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub bumbum()
   Dim Ary As Variant
   With Sheets("Sheet1")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   MsgBox Join(Ary, "&")
End Sub
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
22
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub bumbum()
   Dim Ary As Variant
   With Sheets("Sheet1")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   MsgBox Join(Ary, "&")
End Sub
Thank you so much for your fast & simple solution. God bless you 🙏
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
22
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
Hi Fluff, your code return error if my data is only A1. Can you help again please.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
How about
VBA Code:
Sub bumbum()
   Dim Ary As Variant
   With Sheets("Sheet1")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   If IsArray(Ary) Then MsgBox Join(Ary, "&") Else MsgBox Ary
End Sub
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
22
Office Version
365
Platform
Windows
How about
VBA Code:
Sub bumbum()
   Dim Ary As Variant
   With Sheets("Sheet1")
      Ary = Application.Transpose(.Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value)
   End With
   If IsArray(Ary) Then MsgBox Join(Ary, "&") Else MsgBox Ary
End Sub
How can i implement the Ary to

.Subject in outlook email, i need to take the Ary with "&" if my data is from A1:A2 and take only Ary if my data is from A1

My previous code was

.Subject = "Hello" & "-" & Join(Ary, " & ")

Thank you so much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
Just replace the msgbox with the subject line.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,521
Office Version
365
Platform
Windows
Use
VBA Code:
If IsArray(Ary) Then .Subject ="Hello -"& Join(Ary, "&") Else .Subject= "Hello -" & Ary
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,966
Messages
5,508,438
Members
408,686
Latest member
celobacara

This Week's Hot Topics

Top