Cell references and string concatentation

amt0803

New Member
Joined
Jul 23, 2005
Messages
33
If you have a bunch of names down column A, and want to write a macro that adds the value of another cell to the end of each name, what would you write? Appreciate your help.

For example, in column A:

Bob
Bill
James
Ted

In column D:
2
7
9
0

After the macro runs, I want column A to read:

Bob2
Bill7
James9
Ted0

Assume it's a list of 5000 names though, so you can't write a line for each name. Thanks.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Make use of a blank "helper" column. For example :-

Code:
Dim rng As Range
Set rng = Range([A1], [A65536].End(xlUp))
With rng.Offset(0, 2)
    .FormulaR1C1 = "=RC[-2]&RC[-1]"
    rng.Value = .Value
    .ClearContents
End With
 

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
If you do not want to use a "helper" column... select the cells you want to add the values to (column A in your example) and run this macro.

Code:
Public Sub append_val()
on error resume next

    'adjust offset as needed...
    myOff = 3

    For Each c In Selection
        c.Value = c.Value & Cells(c.Row, c.Column + myOff).Value
    Next c

End Sub
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
My interpretation of the original post was that a loop was to be avoided (presumably to reduce run-time, although I doubt it makes much difference).

Except that, in the event that a whole column is selected, might be better to use :-

Code:
For Each c In Intersect(Selection, ActiveSheet.UsedRange)

Also, since your code is looping through all selected cells, would be better to include a check that only one column has been selected.
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328

ADVERTISEMENT

Make use of a blank "helper" column. For example :-

Code:
Dim rng As Range
Set rng = Range([A1], [A65536].End(xlUp))
With rng.Offset(0, 2)
    .FormulaR1C1 = "=RC[-2]&RC[-1]"
    rng.Value = .Value
    .ClearContents
End With

The fourth line should read :-

.FormulaR1C1 = "=RC[-2]&RC[1]"
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

without helper column
the loop is performed in memory (using an array)
so this is very fast
Code:
Option Explicit

Sub test()
Dim rngA As Range
Dim rngD As Range
Dim arrA As Variant
Dim arrD As Variant
Dim i As Long
Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Set rngA = Range("A1:A" & LR)
Set rngD = Range("D1:D" & LR)
arrA = rngA
arrD = rngD

    For i = 1 To LR
    arrA(i, 1) = arrA(i, 1) & arrD(i, 1)
    Next i

rngA = arrA

End Sub
for more info on this technique see http://puremis.net/excel/code/053.shtml
kind regards,
Erik
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328

ADVERTISEMENT

without helper column
the loop is performed in memory (using an array)
so this is very fast

I would be willing to bet that on most users' computers there would be no noticeable difference in the run-times(helper column vs. array), and the difference is likely to be milliseconds - one way or the other.

One other unrelated point is that both my code and Erik's code fail if the whole of column A contains data. :)
To overcome this, the column A range could possibly be set as :-

Intersect(Columns(1),Activesheet.UsedRange)
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I would be willing to bet that on most users' computers there would be no noticeable difference in the run-times(helper column vs. array), and the difference is likely to be milliseconds - one way or the other.
I don't like to bet. For me scientific results count: you could test and report the results, including testprocedure ...That would be intresting.
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Did it - with 65535 rows.

No noticeable difference.

(You could also have tested and reported - since you were the one claiming "very fast" versus the helper column! :) )
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Did it - with 65535 rows.

No noticeable difference.

(You could also have tested and reported - since you were the one claiming "very fast" versus the helper column! :) )
"no helper column" was meant as comparing to the other code without helper column

I really don't know for sure what's faster, but a statement like "Did it - no noticeable difference" is not scientific.
I would construct a loop around the code and time it
Code:
starttime = Timer
For i = 1 to 100
'code
Next i
MsgBox Timer - starttime
to repeat several times: changing computer performance due to "invisible tasks" at a given time can involve different speed

finally the conclusion will probably be in "most of the cases":
we use the method which is the most usable in the project, regardless of 20% speeddifference
 

Forum statistics

Threads
1,141,756
Messages
5,708,329
Members
421,565
Latest member
Lastadiego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top