Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Concatenate values using VBA

Posted on 2011-09-19
7
320 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:AndreasHermle
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36561417
Andreas - what's wrong with the formula you already have?
0
 
LVL 33

Accepted Solution

by:
jppinto earned 200 total points
ID: 36561446
Try with this code. I've attached a sample file for this.

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Range("C4:C100")

    If Not Intersect(Target, rng) Is Nothing Then
        Target.Offset(0, 2).Value = Target.Value & Chr(10) & Target.Offset(0, 1).Value
        Target.EntireRow.AutoFit
    End If

End Sub

Open in new window

VBA-To-Enter-Formula.xls
0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 200 total points
ID: 36561514
I see now, you did it manually? Perhaps this:
With Columns(3).SpecialCells(xlCellTypeConstants)
    .Offset(, 2).Formula = "=" & .Cells(1).Address(0, 0) & " & Char(10) & " & _
                   .Cells(1).Offset(, 1).Address(0, 0)
End With

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 5

Expert Comment

by:slycoder
ID: 36561550
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
0
 
LVL 5

Expert Comment

by:slycoder
ID: 36561752
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
0
 
LVL 5

Assisted Solution

by:slycoder
slycoder earned 100 total points
ID: 36561784
The function above will set the cell references to absolute($C$4), if you want them to be relative (C4) - you can make the following change:

            ' Combine columns with a carriage return separation
            myCell.Offset(0, 3).Formula = "=" & myCell.Address(0, 0) & " & Char(10) & " & _
                   myCell.Offset(0, 1).Address(0, 0)

Thanks
0
 

Author Comment

by:AndreasHermle
ID: 36563031
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question