Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Concatenate values using VBA

Dear Experts:

I got values in Column C and D

As soon as the macro hits an entry in Column C …
... the macro is to enter a formula in Column E concatenating the values in Column C and D with the values separated by a line break (e.g. C5&Chr(10)&D5 or C10&Chr(10)&D10)
and so forth.

I have attached a sample file for your convenience.

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas

 VBA-To-Enter-Formula.xls
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Andreas - what's wrong with the formula you already have?
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Carlos Ramirez
This makes the column values static -

Option Explicit

Sub ConcatColumns()
'
    Dim myLastRow As Integer
    Dim myRange As Range
    Dim myCell As Range
   
    ActiveCell.SpecialCells(xlLastCell).Select
   
    ' Determine last row
    myLastRow = ActiveCell.Row
   
    ' Select column c
    Range("C1:C" & myLastRow).Select
   
    ' Cycle through each cell
    For Each myCell In Selection
   
        ' set contents of Column F
        If myCell.Value <> "" Then
            ' Combine columns with a carriage return separation
            myCell.Offset(0, 3) = myCell.Value & vbCrLf & myCell.Offset(0, 1).Value
        End If
    Next myCell
   
End Sub
You did say formula - so StephenJR's method will work (and should receive the points).

Implementing his method with my function:

Option Explicit

Sub ConcatColumns()
'
    Dim myLastRow As Integer
    Dim myCell As Range
   
    ActiveCell.SpecialCells(xlLastCell).Select
   
    ' Determine last row
    myLastRow = ActiveCell.Row
   
    ' Select column c
    Range("C1:C" & myLastRow).Select
   
    ' Cycle through each cell
    For Each myCell In Selection
   
        ' set contents of Column F
        If myCell.Value <> "" Then
       
            ' Combine columns with a carriage return separation
            myCell.Offset(0, 3).Formula = "=" & myCell.Address & " & Char(10) & " & _
                   myCell.Offset(0, 1).Address
                   
            ' use the line below for static values
            ' myCell.Value & vbCrLf & myCell.Offset(0, 1).Value
        End If
    Next myCell
   
End Sub
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

Dear All:

Great help from all of  you.  Thank you very much for  your professional support. I suggest splitting the point since all approaches work just fine, although I slightly favour jppinto's and StephenJR's approach since no selection is involved.

Again, thank you very much for your great support.
Regards, Andreas