ExcelBanter - worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyArray() As Variant
Static idex As Long
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler

If Len(Trim(Range("A1"))) = 0 Then
If Target.Address < "$A$1" Then
If idex = 0 Then
idex = 1
Else
idex = idex + 1
End If
MsgBox idex
ReDim Preserve MyArray(1 To idex)
MyArray(idex) = Target.Value
End If
ElseIf InStr(1, Range("A1"), "write", vbTextCompare) Then
Application.EnableEvents = False
Range("M1").Resize(idex).Value = _
Application.Transpose(MyArray)
ElseIf InStr(1, Range("A1"), "clear", vbTextCompare) Then
Columns("M").ClearContents
Erase MyArray
idex = 0
End If
ErrHandler:
Application.EnableEvents = True
End Sub

If you type "write" in A1 it writes the array in column M. If you type
"clear" in A1, it clears the array. If A1 is blank, it records the entry
made in the array.

If you want the array accessible from other procedures, then you need to
declare it as a public variable at the top of a general module
(insert=module) along with idex and remove the declarations from the Change
event.

--
Regards,
Tom Ogilvy

" wrote:

HI, Is it possible with worksheet change to fill a vba array and retain
those values in the array with pasting back to a worksheet. What I meen
is, Cell changes in worksheet, Change fired, my array = 1, end sub,
back to worksheet, cell changes, Change fired no myarray = myarray + 1.
At the moment I assume because of end sub the array is empty.
Regards Robert