help desperate, array not found in excel

Posted by Rob on December 31, 2001 10:44 AM

I have a large spreadsheet in which I want to insert new rows. I use quite a few arrays with linked worksheets.

I now have a problem whereby excel will not permit a row to be inserted. It gives message "you cannot change part of an array" When I use the goto, special, current array command, to find the extend of the array, excel indicates that no array is found.

Any ideas folks? Is spreadsheet or workbook corrupted perhaps? I'm desperate. It took me years to develop this workbook.

Posted by Gary Bailey on December 31, 2001 12:11 PM

The "goto, special, current array" will only find the array that overlaps the activecell. The array thats causing you bother could be anywhere in the row (look for the {curly brackets}).

If you really can't find it try the following macro

Sub FindArray()

Dim rngCell As Range

For Each rngCell In ActiveCell.EntireRow.Cells
If rngCell.HasArray Then
MsgBox "Array is at " & rngCell.CurrentArray.Address
End If
Next rngCell

End Sub

Gary