Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
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 wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
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 wrote in message ups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
did you try:
xlShiftUp -- Gary wrote in message ups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
fill array
Thanks for you help and advice, much appreciated. All my formulas ok
now. Regards Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill in cells with an array | Excel Discussion (Misc queries) | |||
fill down array to last available row | Excel Worksheet Functions | |||
Fill an Array with String values | Excel Programming | |||
Fill Array from a CSV File | Excel Programming | |||
Fill Searched array with text | Excel Programming |