John,

your basic mistake is that your array has NumRows - 1 elements, because you
set it equal to a range from row 2 to row NumRows.

So, you could use

For i = 1 To numRows - 1

Or, better I think, would be to use the range object instead of an array:


To do that, instead of

Dim myArray As Variant
and
ReDim myArray(numRows, 7)
myArray = Sheets("Account Activity").Range(Cells(2, 1), Cells(numRows, 7))

Use

Dim myArray As Range
Set myArray = Sheets("Account Activity").Range(Cells(2, 1), Cells(numRows,
7))

then use

For i = 2 To numRows

instead of

For i = 1 To numRows




HTH,
Bernie
MS Excel MVP


"John Pierce" wrote in message
...
The following procedure works perfectly except that it generates a
"Subscript out of range" error message when it finishes, which doesn't
affect its performance but is annoying. Also, I am pretty sure this
code could be cleaned up and straightened out by someone with more
knowledge than I have. Any help would be appreciated.

Public Sub CopyAccountActivitytoTransactions()
Dim myArray As Variant
Dim numRows As Long
Dim i As Integer
Dim ShX As Worksheet
Dim StartHere As Integer

Set ShX = Worksheets("Transactions")

Worksheets("Account Activity").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
numRows = Selection.Rows.Count
Range("A1").Select

ReDim myArray(numRows, 7)

myArray = Sheets("Account Activity").Range(Cells(2, 1), Cells
(numRows, 7))

ShX.Activate
ActiveSheet.Range("A65536").Select
Selection.End(xlUp).Select
' ActiveCell.Offset(1, 0).Range("A1").Activate
StartHere = ActiveCell.Offset(1, 0).Row - 1

With ActiveSheet
For i = 1 To numRows
.Cells(i + StartHere, 1) = myArray(i, 4)
.Cells(i + StartHere, 2) = "=IF(AND
(Event=""Transfer"",Amount<0),""Sell Shares"",IF
(Event=""Dividend"",""Reinvest"",""Buy Shares""))"
.Cells(i + StartHere, 3) = myArray(i, 2)
.Cells(i + StartHere, 4) = ""
.Cells(i + StartHere, 5) = myArray(i, 1)
.Cells(i + StartHere, 6) = myArray(i, 5)
.Cells(i + StartHere, 7) = myArray(i, 7)
.Cells(i + StartHere, 8) = myArray(i, 6)
.Cells(i + StartHere, 9) = "=IF(Security=""Some Stock
Fund"",Amount/SharePrice,UnitsShares)"
.Cells(i + StartHere, 10) = "=IF(Security=""Some Stock
Fund"",VLOOKUP(ProcessDate,Prices,5,FALSE),UnitPri ce)"
.Cells(i + StartHere, 11) = myArray(i, 3)
Next i
End With
ActiveCell.Select
End Sub