CODEWITHSUNDEEP
excelvba
JackReacher
JackReacher

Reputation: 465

How to concatenate text from a column into a new column? VBA Excel

I'm new to programming and I would like to work on a function to fix salutations in an excel file.

To start, I would just like to append a Dear " to a name in the first column, and put this value in the next column, so that I would end up with the name in the first column and "Dear name" in the next column.

The function I have so far, is putting "Dear " in the next column, but it is not appending that to the text in the first column. Could someone help me correct my code?

Sub letterSalutationFixer()

      Dim letterSalutationColumn As Range
      Set letterSalutationColumn = Columns(1)

       For Each Cell In letterSalutationColumn
            Cell.Offset(, 1).Value = "Dear " & Cell.Text    
      Next

    End Sub

PS. I do realise that I don't necessarily need to do this programmatically since it doesn't take that long to do with the functions already available, but I eventually want to expand this to fix other data with more complexity - and just thought I could start with something simple.

Many thanks in advance!

Upvotes: 1

Views: 2521

Answers (2)

brettdj
brettdj

Reputation: 55672

Another way is the VBA alternative of

  1. Using a formula in column B that runs the concatenation against the used part of column A (ie in B1 ="Dear " &A1 etc)
  2. The formula then is copied over itself as a value to remove the formula

code

Sub QuickCon()
Dim rng1 As Range
Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
With rng1.Offset(0, 1)
.FormulaR1C1 = "=""Dear "" &RC[-1]"
.Value = .Value
End With
End Sub

Upvotes: 3

Doug Glancy
Doug Glancy

Reputation: 27478

The reason it's blank is that Cell is equivalent to the whole column. You're close though. If you did...

For Each Cell In letterSalutationColumn.Cells

..l it would cycle through each cell.

However, the way it's written, it would cycle through each cell in the whole column, which could crash Excel, or at least slow things way down.

Here's a reworked version of what you're trying to do. It only acts on the cells in column A with content:

Sub Salutation()
Dim ws As Excel.Worksheet
Dim LastRow As Long
Dim NameRange As Excel.Range
Dim cell As Excel.Range

Set ws = ActiveSheet
With ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set NameRange = .Range("A2:A" & LastRow)
    For Each cell In NameRange
        cell.Offset(, 1) = "Dear " & cell.Text
    Next cell
End With
End Sub

It also declares all variables, something you want to get in the habit of doing. Do a search on Option Explicit to learn how to force yourself to.

It also uses a With statement to fully qualify Object references, so that instead of just referring to Column(1) or Range(something) you're specifying that it's in ws, which has been set to the ActiveSheet.

Upvotes: 3

Related Questions