Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range, and more
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range? | Excel Programming | |||
Subscript out of Range | Excel Programming | |||
Subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming | |||
SubScript Out Of Range. | Excel Programming |