Need help adding CONCATENATE formula to cell using VBA

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
281
Hello all,

I'm trying to add a formula that adds the first and last names to a cell. It works fine but I'm having trouble adding a space between the names. Any ideas on the correct syntax?


Code:
Sub FullName()

Dim LastRow As Long
 LastRow = Range("AJ" & Rows.Count).End(xlUp).Row
Range("BH1").Select
    ActiveCell.FormulaR1C1 = "Full Name"
Range("BH2").Select
    ActiveCell.Formula = "=concatenate(C2&""&D2)"
'Range("BH2").Select
'    Selection.AutoFill Destination:=Range("BH2:BH" & LastRow)
Range("BH2").AutoFill Destination:=Range("BH2:BH" & LastRow)
End Sub

Cheers,

Jed
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,516
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
"=concatenate(C2&"" ""&D2)"
 

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
281
Aaaaarrrrgggghhhhh!!! Quotes in quotes! It's the only combinination I didn't try! XD
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,109,306
Messages
5,527,912
Members
409,793
Latest member
shawnash

This Week's Hot Topics

Top