Creating a cell so data goes into a table
 

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

no it needs to go into a table 10 x 10 and each figure entered needs to go
into one of those cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

the 10 x 10 starts blank. everytime i enter a value in a cell i want it to go
to the first cell in the table. then i want the value i enter next from the
same cell to go into the next cell on the table.
10 x 10 is a practice but i want the table to grow until i enter all my values

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

a brute force macro to do so would be like

assume C3 is the cell you want to enter into the table D1:M10
Sub fltab()
If Cells(3, 3) = "" Then GoTo 100
cnt = Application.WorksheetFunction.CountA(Range("D1:M10 "))
If cnt = 0 Then rw = 1: cl = 0: GoTo 10
cl = Application.WorksheetFunction.Floor(cnt - 1, 10)
rw = cnt - cl * 10 + 1
10 Cells(rw, 4 + cl) = Cells(3, 3)
If cnt = 100 Then Cells(3, 3) = "FULL" Else Cells(3, 3) = ""
100
End Sub

ways of running the sub would be to have a button next to the cell with the
macro assigned.
or the macro could be modified and run from the sheet module as a change
event that would run whenever somthing was put into the cell.

{I like brute force macros to try things out, but perfer to make the more
formal macros with dim statements and as on action events once they actually
work the way I want them. Brute force macros are easier to understand and
for simple ones to debug, but are more likely to have certain types of
problems.}




"hally" wrote:

the 10 x 10 starts blank. everytime i enter a value in a cell i want it to go
to the first cell in the table. then i want the value i enter next from the
same cell to go into the next cell on the table.
10 x 10 is a practice but i want the table to grow until i enter all my values

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

how would you create that?

"TomHinkle" wrote:

I'd just use the data form...
Simple and no coding..

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

could you please go through most stages on how i get the macro to run as i
dont know much about macros.

"bj" wrote:

a brute force macro to do so would be like

assume C3 is the cell you want to enter into the table D1:M10
Sub fltab()
If Cells(3, 3) = "" Then GoTo 100
cnt = Application.WorksheetFunction.CountA(Range("D1:M10 "))
If cnt = 0 Then rw = 1: cl = 0: GoTo 10
cl = Application.WorksheetFunction.Floor(cnt - 1, 10)
rw = cnt - cl * 10 + 1
10 Cells(rw, 4 + cl) = Cells(3, 3)
If cnt = 100 Then Cells(3, 3) = "FULL" Else Cells(3, 3) = ""
100
End Sub

ways of running the sub would be to have a button next to the cell with the
macro assigned.
or the macro could be modified and run from the sheet module as a change
event that would run whenever somthing was put into the cell.

{I like brute force macros to try things out, but perfer to make the more
formal macros with dim statements and as on action events once they actually
work the way I want them. Brute force macros are easier to understand and
for simple ones to debug, but are more likely to have certain types of
problems.}




"hally" wrote:

the 10 x 10 starts blank. everytime i enter a value in a cell i want it to go
to the first cell in the table. then i want the value i enter next from the
same cell to go into the next cell on the table.
10 x 10 is a practice but i want the table to grow until i enter all my values

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

I'd just use the data form...
Simple and no coding..

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

create column headings. (preferably in the top row)

with your cursor on one of the headings, click the data menu, then form..
If you don't have any data yet, Excel will ask you where the headings are,
just click okay.






"hally" wrote:

how would you create that?

"TomHinkle" wrote:

I'd just use the data form...
Simple and no coding..

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.

go to <tools<macros<visual Basic editor
<insert<module
copy the macro code from the response and paste into the module
You will probably need ot change the diference range references to what you
are actually using in your file.
<file<close and return
select a character from autoshapes and put next to your cell
right click on it and assign the macro you just pasted into the module.


"hally" wrote:

could you please go through most stages on how i get the macro to run as i
dont know much about macros.

"bj" wrote:

a brute force macro to do so would be like

assume C3 is the cell you want to enter into the table D1:M10
Sub fltab()
If Cells(3, 3) = "" Then GoTo 100
cnt = Application.WorksheetFunction.CountA(Range("D1:M10 "))
If cnt = 0 Then rw = 1: cl = 0: GoTo 10
cl = Application.WorksheetFunction.Floor(cnt - 1, 10)
rw = cnt - cl * 10 + 1
10 Cells(rw, 4 + cl) = Cells(3, 3)
If cnt = 100 Then Cells(3, 3) = "FULL" Else Cells(3, 3) = ""
100
End Sub

ways of running the sub would be to have a button next to the cell with the
macro assigned.
or the macro could be modified and run from the sheet module as a change
event that would run whenever somthing was put into the cell.

{I like brute force macros to try things out, but perfer to make the more
formal macros with dim statements and as on action events once they actually
work the way I want them. Brute force macros are easier to understand and
for simple ones to debug, but are more likely to have certain types of
problems.}




"hally" wrote:

the 10 x 10 starts blank. everytime i enter a value in a cell i want it to go
to the first cell in the table. then i want the value i enter next from the
same cell to go into the next cell on the table.
10 x 10 is a practice but i want the table to grow until i enter all my values

"bj" wrote:

does the 10 by 10 start out blank?
I can not tell whether the value goes to every cell at the same time or if
it goes into a different cell each time a new value is entered.
What do you want done after the 10 by 10 block is filled?

"hally" wrote:

needs to go into a 10 x 10 table in which each piece of data is put into each
of the tables cells

"bj" wrote:

do you have criteria as to where it goes into the table?

"hally" wrote:

i am trying to create if possible a cell so that when i enter data into that
cell and i press return it enters the data into a table, then deletes the
cell where i originaly entered the data so i can enter some new data.