Cell references and string concatentation

[amt0803]

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.

 

[Boller]

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

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]

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.

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]

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 :-

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]

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

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]

Hi,

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

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]

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]

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]

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]

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

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