0

I'm trying to make a macro that will combine two cells, First Name and Last Name, to make Full Name.

I did find a code that does a similar thing, except with no space in between the strings. I tried to edit it to add the space but I think I'm doing it wrong. I'll paste it in in case anyone knows how to edit it to add the spaces.

Obviously if that code was not made to add spaces, please let me know if there is one that does!

Thank you!

(Here's the code for no spaces)

Sub BacsRef()
    Dim rSelected As Range
    Dim c As Range
    Dim sArgs As String
    Dim bCol As Boolean
    Dim bRow As Boolean
    Dim sArgSep As String
    Dim sSeparator As String
    Dim rOutput As Range
    Dim vbAnswer As VbMsgBoxResult
    Dim lTrim As Long
    Dim sTitle As String
   
    Set rOutput = ActiveCell
    bCol = False
    bRow = False
    sSeparator = ""
    sTitle = IIf(bConcat, "CONCATENATE", "Ampersand")
    On Error Resume Next
    Set rSelected = Application.InputBox(Prompt:= _
                    "Select cells to create formula", _
                    Title:=sTitle & " Creator", Type:=8)
    On Error GoTo 0
    If Not rSelected Is Nothing Then
        sArgSep = IIf(bConcat, ",", "&")
        If bOptions Then
            vbAnswer = MsgBox("Columns Absolute? $A1", vbYesNo)
            bCol = IIf(vbAnswer = vbYes, True, False)
            vbAnswer = MsgBox("Rows Absolute? A$1", vbYesNo)
            bRow = IIf(vbAnswer = vbYes, True, False)
            sSeparator = Application.InputBox(Prompt:= _
                        "Type separator, leave blank if none.", _
                        Title:=sTitle & " separator", Type:=2)
        End If
        For Each c In rSelected.Cells
            sArgs = sArgs & c.Address(bRow, bCol) & sArgSep
            If sSeparator <> "" Then
                sArgs = sArgs & Chr(34) & sSeparator & Chr(34) & sArgSep
            End If
        Next
        lTrim = IIf(sSeparator <> "", 4 + Len(sSeparator), 1)
        sArgs = Left(sArgs, Len(sArgs) - lTrim)
        If bConcat Then
            rOutput.Formula = "=CONCATENATE(" & sArgs & ")"
        Else
            rOutput.Formula = "=" & sArgs
        End If
    End If
End Sub

Edit: I did it myself! I added a space in the line " sSeparator = "" " within the quotation marks!

DS_London
  • 3,644
  • 1
  • 7
  • 24
ImFamousISwear
  • 1
  • 2
  • Why don't you record your own action and after that inspect the resulting code? – Reporter Sep 06 '21 at 15:25
  • 3
    Use `TEXTJOIN` or [this UDF version](https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell). – BigBen Sep 06 '21 at 15:26
  • Depending on where and in what form you require the resulting full name for, you can achieve this without VBA in a formula: `=A1 & " " & B1` (assuming A1 is first name and B1 is last name) – Franz Reischl Sep 07 '21 at 09:37

0 Answers0