Geek Answers Handbook

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

I have two cells allow me to say: A1 and A2

The content of each of them is a line:

A1: Hallo

A2: World

My goal is to combine the contents of A1 and A2 in another cell, for example. A3, that is, the content of A3 should be:

Hallo world

I would like to do this with a VBA macro, not just for strings as content.

Thank you for your answers !!

+4
vba excel-vba
source share
3 answers

Although, as MasterMix says, this is most easily achieved using a formula, if you have a reason to use VBA, it depends on how you want to specify the cells.

You can do this as a function:

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

All this makes replication (much faster) the built-in Excel concatenation function.

You can also make this one of about a hundred ways in the procedure, here one of the methods will tell the user 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 can also use predefined ranges and scroll through them if you have multiple ranges to combine. If you explain a little more about the script, then someone can provide more specific code.

+6
source share

I suggest an Excel formula

=A1&A2

or VBA macro

 Range("A3").Cell.Value = Range("A1").Cell.Value & Range("A2").Cell.Value
+3
source share

It's faster, just select the cells and they are merged into the first cell.

 '------------------------------------------------------------------------ ' Procedure : Concatenate Text ' Author : Tim Bennett ' Date : 11/6/2015 ' Purpose : Concatenate selected text into first column '------------------------------------------------------------------------ ' 'Sub Concatenate_Formula(bConcat As Boolean, bOptions As Boolean) Sub Concatenate() Dim rSelected As Range Dim c As Range Dim sArgs As String Dim bCol As Boolean Dim bRow As Boolean 'Set variables Set rOutput = ActiveCell bCol = False bRow = False On Error Resume Next 'Use current selection Set rSelected = Selection On Error GoTo 0 'Only run if cells were selected and cancel button was not pressed If Not rSelected Is Nothing Then sArgs = "" 'Create string of cell values firstcell = "" For Each c In rSelected.Cells If firstcell = "" Then firstcell = c.Address(bRow, bCol) sArgs = sArgs + c.Text + " " 'build string from cell text values c.Value = "" ' Clear out the cells taken from Next 'Put the result in the first cell Range(firstcell).Value = sArgs End If End Sub
0
source share

More articles:


All Articles