Array formula

D

Dennis Saunders

I have a 2 column list of (A)surname (B) forename. This list is changed
weekly. I want to write some code that concatenates col B,A as an array
formula but I'm stuck on the syntax as I want to use a variable.
So far it looks like: (where Sag = CountA(Names in Column A))

Dim Sag As Integer
Sag = Sheets("Variables").Range("A19")
Range(Cells(2, 4), Cells(Sag, 4)).Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(Range(Cells(2, 2):Cells(2, Sag)),Range(Cells(2,
1):Cells(2, Sag)))"
Selection.FormulaArray = _
"=CONCATENATE(Range(Cells(2, 2):Cells(2, Sag)),Range(Cells(2,
1):Cells(2, Sag)))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

It must be something simple.
 
D

Dave Peterson

I'm not sure why you'd need an array formula.

How about something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Sheet1")
With wks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("c2:C" & LastRow)
.FormulaR1C1 = "=rc[-1]&"",""&rc[-2]"
.Value = .Value
End With
End With
End Sub

And if you didn't want that comma as a separator:

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

I used Sheet1 as the sheet that contained the data.
 
D

Don Guillett

One way
Sub mergeem()
For Each c In Range("a10:a" & Cells(Rows.Count, 1).End(xlUp).Row)
c.Offset(0, 2) = c.Offset(0, 1) & "," & c.Offset(0, 0)
'or
'c.Offset(0, 2) = c.Offset(0, 0) & " " & c.Offset(0, 1)
Next
End Sub
 
D

Dennis Saunders

Thanks very much.
For some reason I thought it was quicker/tidier to use array formulae. I use
quite a few formulae which fill down to a certain cell (relative to a
variable) and thought I could do that for this formula.hmmm!
Regards Dennis
 
D

Dave Peterson

Ah, when you fill that range with formulas (or anything else), do you really hit
ctrl-shift-enter (to make it an array formula) or do you just hit ctrl-Enter
(that fills the range)?
 
D

Dennis Saunders

Thanks Don and Dave....it's taken me some time but I've cracked it now.
Obviously?!!! ;-)

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
I can use this instead of messing around with a variable (counting cells in
a column).
This Week, Mainly, I shall be adjusting my code.


Regards Dennis
 
Top