fill array

Hi Im wondering if this is possible. Using worksheet change, if I enter
a number in cell b2 (doesnt matter were), that number is stored into an
array called 'My Numbers'. In same cell I enter a new number, this
number is entered to 'my numbers', retaining the first number. I now
have 2 numbers, I do this upto 50 numbers. So now I have in 'my
numbers', 50 numbers. When I enter the 51st number into the cell, Id
like 'my numbers' to drop the first number and add the new number.
Regards Robert

Are you talking about a VBA array or a range of cells someplace.

>if I enter
> a number in cell b2 (doesnt matter were)

What does that statement mean? enter a value in B2 In any open workbook,
any sheet?

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Hi Im wondering if this is possible. Using worksheet change, if I enter
> a number in cell b2 (doesnt matter were), that number is stored into an
> array called 'My Numbers'. In same cell I enter a new number, this
> number is entered to 'my numbers', retaining the first number. I now
> have 2 numbers, I do this upto 50 numbers. So now I have in 'my
> numbers', 50 numbers. When I enter the 51st number into the cell, Id
> like 'my numbers' to drop the first number and add the new number.
> Regards Robert
>

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

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




<[email protected]> wrote in message
news:[email protected]...
> 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
>

Thanks Tom, This great. Much cleaner. I couldnt get .Range("A1").Delete
shift:=xlShiftDown to work, so I deleted the shift part and it works
ok. Graphing good as well. Can you see a problem with leaving the shift
part of. If I want do some calculating in the worksheet with these
figures, do I have to use Inderect in my formulas, as with the graphs.
Thanks for your advise.
Regards Robert

Sorry, it should have been

Range("A1").Delete Shift:=xlShiftUp (not down)

anyway, that must be the default, so it doesn't need to be specified.

If you don't want your formulas to adjust when you delete the cell, then you
would need to use indirect. Even an absolute reference will be adjusted
when you selete a cell that would affect the cell it refers to. Since the
argument to indirect is a string (not a reference to a cell), it is not
affected.

--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> Thanks Tom, This great. Much cleaner. I couldnt get .Range("A1").Delete
> shift:=xlShiftDown to work, so I deleted the shift part and it works
> ok. Graphing good as well. Can you see a problem with leaving the shift
> part of. If I want do some calculating in the worksheet with these
> figures, do I have to use Inderect in my formulas, as with the graphs.
> Thanks for your advise.
> Regards Robert
>

did you try:

xlShiftUp

--


Gary


<[email protected]> wrote in message
news:[email protected]...
> Thanks Tom, This great. Much cleaner. I couldnt get .Range("A1").Delete
> shift:=xlShiftDown to work, so I deleted the shift part and it works
> ok. Graphing good as well. Can you see a problem with leaving the shift
> part of. If I want do some calculating in the worksheet with these
> figures, do I have to use Inderect in my formulas, as with the graphs.
> Thanks for your advise.
> Regards Robert
>

Thanks for you help and advice, much appreciated. All my formulas ok
now.
Regards Robert