Combine 2 var into 1 string with & in between

bumbum2812

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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,273
Office Version
  1. 365
Platform
  1. 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
24
Office Version
  1. 365
Platform
  1. 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
46,273
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

bumbum2812

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
46,273
Office Version
  1. 365
Platform
  1. 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
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
46,273
Office Version
  1. 365
Platform
  1. Windows
Just replace the msgbox with the subject line.
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,108,943
Messages
5,525,776
Members
409,663
Latest member
littleriver

This Week's Hot Topics

Top