Andreas Hermle
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
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
Andreas - what's wrong with the formula you already have?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This makes the column values static -
Option Explicit
Sub ConcatColumns()
'
Dim myLastRow As Integer
Dim myRange As Range
Dim myCell As Range
ActiveCell.SpecialCells(xl LastCell). 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
Option Explicit
Sub ConcatColumns()
'
Dim myLastRow As Integer
Dim myRange As Range
Dim myCell As Range
ActiveCell.SpecialCells(xl
' 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(xl LastCell). 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
Implementing his method with my function:
Option Explicit
Sub ConcatColumns()
'
Dim myLastRow As Integer
Dim myCell As Range
ActiveCell.SpecialCells(xl
' 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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