Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
change font color of first cell in data table attached to chart | Charts and Charting in Excel | |||
Repeat Cell Data | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Formula to Extract Data from a Table | Excel Worksheet Functions |