fill array
I would delete A1 Assume the data is stored in a range
With worksheets("Data")
.Range("A1").Delete shift:=xlShiftDown
.Range("A50").Value = newvalue
End with
If you have to initially populate the list before it gets full
With worksheets("Data")
if isempty(.range("A1") then
.Range("A1").Value = Newvalue
elseif Application.Count(.Range("A1:A50") < 50
set rng = .Range("51").End(xlup)(2)
rng = NewValue
else
.Range("A1").Delete shift:=xlShiftDown
.Range("A50").Value = newvalue
end if
End with
The source for the graph would be a name range
Insert = Name = Define
name: Array1
refersto: =Indirect("Data!A1:A50")
defined this way, the reference won't be affected by deleting the cell.
--
Regards,
Tom Ogilvy
wrote in message
ups.com...
Tom, thanks for the reply. I meen enter a value in a cell in a
worksheet in a specific workbook. B2 was just an example. First of I
was talking about a vba array, but if its possible to do in a range of
cells without copying and pasting that would be good. Basically the
data is entered every 1 to 2 secs from software, whence using
worksheet_change. Im doing this at the momment by copying the data when
it changes, pasting it into another wroksheet. In cells a1:a50, when I
first open the workbook I fill with '0' then the first value is pasted
to a51, a2:a51 is selected and cut. Then pasted back to a1. A1:a50 is
linked to a graph in second workbook and as long as I dont click on
that workbook, the graph updates. If I click on the second workbook the
macro fails, I think because its looking for info thats not there. Im
working on that now. As you can see, messy. Whence seeing if I can fill
an array, even if I pasted that back to the worksheet, its got to be
better.
Regards Robert
|