Merge the contents of 2 cells into another 3rd cell using VBA in Excel

Question

I have two cells lets say: A1 and A2

The content of each one of them is a string:

A1: Hallo

A2: World

My goal is to merge the contents of A1 and A2 in another cell e.g. A3 i.e. A3's content should be:

Hallo World

I would like to do this using a VBA macro and not only for strings as contents..

Thanks both of u for your answers!!

Answer 1

Although, as MasterMix says, this is most easily achieved by a formula, if you have a reason why VBA must be used then it depends on how you wish to specify the cells.

You could do this as a function:

Private Function addTwoCells(rngA As Range, rngB As Range) As String
    addTwoCells = rngA & rngB
End Function

All this does is replicate the (much faster) built-in Excel concatenate function though.

You could also do it in one of about a hundred ways in a procedure, here's one way that prompts the user for the ranges:

Private Sub addTwoCellsProc()
    Dim rngA As String
    Dim rngB As String
    Dim rngOutput As String
    Dim rngTest As Range

    Do
        rngA = InputBox("Please enter first cell address", "Cell A")
        rngA = Range(rngA).Cells(1, 1).Address
        Set rngTest = Intersect(Range(rngA).Cells(1, 1), ActiveSheet.Cells)
    Loop Until Not rngTest Is Nothing

    Do
        rngB = InputBox("Please enter second cell address", "Cell B")
        rngB = Range(rngB).Cells(1, 1).Address
        Set rngTest = Intersect(Range(rngB), ActiveSheet.Cells)
    Loop Until Not rngTest Is Nothing

    Do
        rngOutput = InputBox("Please enter destination cell address", "Output cell")
        Set rngTest = Intersect(Range(rngOutput), ActiveSheet.Cells)
    Loop Until Not rngTest Is Nothing

    Range(rngOutput) = Range(rngA) & Range(rngB)
End Sub

You could also use predefined ranges and loop through them if you have multiple ranges to combine. If you explain a bit more about the scenario then someone might provide more specific code.

Answer 2

I suggest either an Excel formula

=A1&A2

or a VBA macro

Range("A3").Cell.Value = Range("A1").Cell.Value & Range("A2").Cell.Value