How to copy multi cell array to last row

[tnewt]

Using excel 2007.
Coulmn A contains a list of names of verying rows.
Coulmn B thru E contains an array to a function (=ParseOutNames(A2)).

How can i automate the copying of array B2 thru E2 to the last row of data in coulmn A. Updating each row to point to that rows data in coulmn A

Would it be better to select the range of cells then fill in the array?

 

[tnewt]

Did not work. This time it looks like all the cells are formated the same.
Row 2 is correct for B2, C2, D2, E2. (pointing to A2)
but B3, C3, D3, E3 points to A2 not A3

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 323px"><COL style="WIDTH: 86px"><COL style="WIDTH: 76px"><COL style="WIDTH: 94px"><COL style="WIDTH: 44px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Original Name</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Last Name</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Fisrt Name</TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Middle Initial</TD><TD style="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Suffix</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Courier New; COLOR: #800000; FONT-SIZE: 12pt">Pearson</TD><TD>Pearson </TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Courier New; COLOR: #800000; FONT-SIZE: 12pt">Pearson, Charles</TD><TD>Pearson </TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Courier New; COLOR: #800000; FONT-SIZE: 12pt">Pearson, Charles H</TD><TD>Pearson </TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Courier New; COLOR: #800000; FONT-SIZE: 12pt">Pearson, Charles Henry</TD><TD>Pearson </TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Courier New; COLOR: #800000; FONT-SIZE: 12pt">Pearson Jr, Charles </TD><TD>Pearson </TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>C2</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>D2</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>E2</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>B3</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>C3</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>D3</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>E3</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>B4</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>C4</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>D4</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>E4</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>B5</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>C5</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>D5</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>E5</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>B6</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>C6</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>D6</TD><TD>{=ParseOutNames(A2)}</TD></TR><TR><TD>E6</TD><TD>{=ParseOutNames(A2)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[pbornemeier]

Getting late here and I am getting punchy.

Sub Macro22()

    Dim lLastColumnADataRow As Long
    lLastColumnADataRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B2:E" & lLastColumnADataRow).FormulaArray = "=ParseOutNames(RC[-1])"
End Sub

 

[tnewt]

I think now we are getting to the real root of my problem. In theory this should work but it yealds the same results as shown in post #11. every thing i have tried just will not copy the array and format it correctly to advance for each row. Not sure what to try next.

 

[ZVI]

Try the modification of Phil's code:

Sub Test()
  Dim lLastColumnADataRow As Long
  lLastColumnADataRow = Cells(Rows.Count, 1).End(xlUp).Row
  Range("B2:E2").FormulaArray = "=ParseOutNames(RC[-1])"
  Range("B2:E2").AutoFill Destination:=Range("B2:E" & lLastColumnADataRow)
End Sub

 

[tnewt]

Sweet... Problem Solved!
Knew I came to the right board for answers.
Thank you!
I have been working on this issue for a long time. I learned new things from both of your responses.
Mrexcel boards Rock! But we all know its people like you two that make it all happen. Thx again for all your time.