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?

 

[pbornemeier]

Welcome to the Board!

If you select B2:E2 then double-click on the handle (small square in the lower right corner of E2) the formulas in B2:E2 will be copied down to the last filled row in column A.

 

[tnewt]

Thank you for the welcome and the very fast responce but i was looking for a automated way. i would like a macro so that i can call it from other macros that are run on the page before the report is complete.

 

[tnewt]

useing your response to record my own macro worked exactly like i asked for.
But when i run the macro on a different set of names in coulmn A it only populates the cells down to the recorded macro rows.
i was using this file to sort the names of mutiple different lists of names with vering number of rows in coulmn A.
Any sugestions to make it always go to the last row of data?

 

[tnewt]

here is the macro that i recorded.

Sub Macro22()
Range("B2:E2").Select
ActiveCell.FormulaR1C1 = "=ParseOutNames(RC[-1])"
Selection.FormulaArray = "=ParseOutNames(RC[-1])"
Selection.AutoFill Destination:=Range("B2:E17")
Range("B2:E17").Select
Range("A2").Select
End Sub

 

[pbornemeier]

Sub Macro22()

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

End Sub

 

[tnewt]

Tested this and it does not format the array properly.
The same results are displayed (last name/B2) in C2, D2 and E2.
all the rows there after also show the same format but does point to the correct same line A coulmn data. And the lines are correctly copied to the last line in coulmn A.

 

[pbornemeier]

To always refer to column A you have to change the RC reference to absolute:
RC[-1] refers to the cell in the same row, previous column (R & C are relative reference)
RC1 refers to the cell in column 1 (column A) of the same row (R is relative, C1 is absolute).

Sub Macro22()

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

 

[tnewt]

Thank you for taking all this time to help me with this issue.
I think i am not providing you with the right explination of my issue.
My macro formats everthing correctly but does not copy to the last row when coulmn A has more or less records.

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 p <>
style="width:323.2px;" /><COLGROUP><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>Charles</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>Charles</TD><TD>H</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>Charles</TD><TD>Henry</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>Charles </TD><TD> </TD><TD>Jr</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(A3)}</TD></TR><TR><TD>C3</TD><TD>{=ParseOutNames(A3)}</TD></TR><TR><TD>D3</TD><TD>{=ParseOutNames(A3)}</TD></TR><TR><TD>E3</TD><TD>{=ParseOutNames(A3)}</TD></TR><TR><TD>B4</TD><TD>{=ParseOutNames(A4)}</TD></TR><TR><TD>C4</TD><TD>{=ParseOutNames(A4)}</TD></TR><TR><TD>D4</TD><TD>{=ParseOutNames(A4)}</TD></TR><TR><TD>E4</TD><TD>{=ParseOutNames(A4)}</TD></TR><TR><TD>B5</TD><TD>{=ParseOutNames(A5)}</TD></TR><TR><TD>C5</TD><TD>{=ParseOutNames(A5)}</TD></TR><TR><TD>D5</TD><TD>{=ParseOutNames(A5)}</TD></TR><TR><TD>E5</TD><TD>{=ParseOutNames(A5)}</TD></TR><TR><TD>B6</TD><TD>{=ParseOutNames(A6)}</TD></TR><TR><TD>C6</TD><TD>{=ParseOutNames(A6)}</TD></TR><TR><TD>D6</TD><TD>{=ParseOutNames(A6)}</TD></TR><TR><TD>E6</TD><TD>{=ParseOutNames(A6)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

This is the results i get when i run the first example macro.
It does everything that i need but the array does not work correctly to return the first name C2, Middle Initial D2 and Suffix E2.
The last name B2 id duplicated to cells C2, D2, E2

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>Pearson </TD><TD>Pearson </TD><TD>Pearson </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>Pearson </TD><TD>Pearson </TD><TD>Pearson </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>Pearson </TD><TD>Pearson </TD><TD>Pearson </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>Pearson </TD><TD>Pearson </TD><TD>Pearson </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>Pearson </TD><TD>Pearson </TD><TD>Pearson </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(B2)</TD></TR><TR><TD>D2</TD><TD>=ParseOutNames(C2)</TD></TR><TR><TD>E2</TD><TD>=ParseOutNames(D2)</TD></TR><TR><TD>B3</TD><TD>=ParseOutNames(A3)</TD></TR><TR><TD>C3</TD><TD>=ParseOutNames(B3)</TD></TR><TR><TD>D3</TD><TD>=ParseOutNames(C3)</TD></TR><TR><TD>E3</TD><TD>=ParseOutNames(D3)</TD></TR><TR><TD>B4</TD><TD>=ParseOutNames(A4)</TD></TR><TR><TD>C4</TD><TD>=ParseOutNames(B4)</TD></TR><TR><TD>D4</TD><TD>=ParseOutNames(C4)</TD></TR><TR><TD>E4</TD><TD>=ParseOutNames(D4)</TD></TR><TR><TD>B5</TD><TD>=ParseOutNames(A5)</TD></TR><TR><TD>C5</TD><TD>=ParseOutNames(B5)</TD></TR><TR><TD>D5</TD><TD>=ParseOutNames(C5)</TD></TR><TR><TD>E5</TD><TD>=ParseOutNames(D5)</TD></TR><TR><TD>B6</TD><TD>=ParseOutNames(A6)</TD></TR><TR><TD>C6</TD><TD>=ParseOutNames(B6)</TD></TR><TR><TD>D6</TD><TD>=ParseOutNames(C6)</TD></TR><TR><TD>E6</TD><TD>=ParseOutNames(D6)</TD></TR></TBODY></TABLE></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]

I was using the first formula in your recorded code in post #5, not the last (corrected) one. Please try this:

Sub Macro22()

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