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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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]"
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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! :) )
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top