Data Entry to a Cell Range

Can I set up a data entry form, so if every time I enter a value in a cell, 
it updates the next empty cell in a range?  Thanks 
0
Utf
3/6/2010 6:02:02 AM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
652 Views

Similar Articles

[PageSpeed] 36

Lets assume that the form is used to update cell A1.  We require that 
everytime A1 is updated the new value will be recorded in column B.  Put the 
following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A1 As Range, t As Range
Set A1 = Range("A1")
Set t = Target
If Intersect(A1, t) Is Nothing Then Exit Sub
Application.EnableEvents = False
    n = Cells(Rows.Count, "B").End(xlUp).Row + 1
    Cells(n, "B").Value = A1.Value
Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

-- 
Gary''s Student - gsnu201001


"Stilltrader47" wrote:

> Can I set up a data entry form, so if every time I enter a value in a cell, 
> it updates the next empty cell in a range?  Thanks 
0
Utf
3/6/2010 12:44:01 PM
Reply:

Similar Artilces:

protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this? Hi select the cells for which you want to allow entries. After this goto 'Format - cells - Protection&...

Data Consolidation
I have 20+ worksheets in a workbook. Each sheet is a timesheet for an individual and each sheet can have up to eight cost codes which will be the same on some sheets but will not be in the same position on all sheets. How do I label or set up a range so I can consolidate all of these cost codes on a seperate sheet to give me the total hours for each cost code? Could you for example do the following: Set up your new sheet with a table with all the cost codes in a column on the left, and all the people at the top. Then put a sumif, or sumproduct formulae so that it adds up all the hours ...

Duplicate Data
One main form with two subforms (linked by ProjectID), first subform is for data entry, the second just allows the user to view what has been saved from the previous subform. The subform allows users to report on a month basis by selecting a month in a combo box [PeriodID]. I would like to add some code in the Befire Update event that checks the PeriodID for a match before it is saved. Can someone help me with the code please, I have tried various DLookup snippets of code from this site, but nothing is working as the code does not really suit my purpose. regards Maybe t...

Synchronizing Outlook Data
Hi, I'm reposting my question; hopefully someone will have a response that will work. I'm trying to synch my notebook PC with my desktop PC to share Outlook information. Is it possible to do this from within Outlook without buying a third-party addon? I have two copies of Outlook 2002, one on my desktop and the other on my notebook PC. I need to be able to use the calander, tasks, contacts, etc on both PCs and I cannot find any information on how to do this. Did I waste my money in buying another copy of Outlook 2002 to run on my desktop? Any help would be greatly appre...

Automatically moving data #2
I was at a halloween party dressed up as a 'killer bunny' One of the guests there told me to imbed "if statements within vlookup but not to use more than 8 if statments. Make any sense to you guys and gals? :confused -- multipla ----------------------------------------------------------------------- multiplan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27391 > I was at a halloween party dressed up as a 'killer bunny' hey, I was at the party too, cloaked as t...

Go to a cell automatically
How can I make the active cell the cell after the statement: "End("B1").xlDown.offset(1,0)" to start a paste operation. Thanks in advance. try range("b1").end(xldown).offset(1).paste "Mercury" <pulgaron@bellsouth.net> wrote in message news:QWlfb.8794$9a7.7822@bignews6.bellsouth.net... > How can I make the active cell the cell after the statement: > "End("B1").xlDown.offset(1,0)" to start a paste operation. > > > Thanks in advance. > > > Try this Range("d1").Copy Range("b1")....

data duplication check ?
Hi all, I have this code which runs a macro to add a complete list of all data for 'today' to be entered into a sepeate worksheet which is called the database. Is there a way, if so how, that the code can be changed so that if the 'same' data is entered twice a pop up message box is shown to alert the user of this and stop this happening. The code I have is Sub add_Anydays_jobs() With ActiveSheet.Range("A8:N34") Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset( _ 1, 0).Resize(.Rows.Count, .Columns.Count)....

adding trendline into data-column
After adding a trendline (moving average), I woudl like to add the values of the trendline into a column..How can I do that?? The chart trendline option is for a simple moving average, e.g. with period 3, the plotted point is the average of the current and previous two points. Just use the average function and copy the formula down. For an exponentially weighted moving average (does not seem to be among the chart options) see http://groups.google.com/groups?selm=409CD102.6050009%40no_e-mail.com Jerry Inge Jonckheere wrote: > After adding a trendline (moving average), I woudl like...

Placing a Date Range on a Report
I have several reports that have been created in Microsoft Access that require a date range in order to pull the requested data. The date range was set up in the query that was built to run the report. Is there a way to get that date range displayed on the report? ...

Display cells with data validation
Using 2003 - Is there a way of displaying cells that contains data validation restrictions? I've received a spreadsheet from someone with lots of restrictions on it and want to see which cells are affected. Thnks Anita "Anita" <Anita@discussions.microsoft.com> wrote in message news:19A5FB74-EF19-40C3-9632-EA0FB7F9D8B0@microsoft.com... > Using 2003 - Is there a way of displaying cells that contains data > validation > restrictions? I've received a spreadsheet from someone with lots of > restrictions on it and want to see which cells are affected. >...

Run a macro when cell value changes
I have a macro that I would like to run, onlly when a particular cel value reaches a pre-defined value. I really do not know how to do this - I have looked on help features but to no avail. any ideas -- Message posted from http://www.ExcelForum.com right click sheet tab>view code>copy/paste this>modify to suit>save Now when cell c1 calculates to >32 your macro will fire Private Sub Worksheet_Calculate() If Range("c1") > 32 Then call yourmacro ' MsgBox "Hi" End Sub -- Don Guillett SalesAid Software donaldb@281.com "Emea training >&quo...

Data file conversion M05 to M04
I am trying to go back from M05 to using M04. Is there utility that converts my M05 data file to M04 format? When you converted to M2005 it made a backup of your M2004 files as *.m12 and told you where it put it (usually same place as .mny). Rename this as *.mny and open with M2004. You will of course have to catch up all transactions since you converted. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes ...

Receiving Transaction Entry
Can anyone tell me what the column RCTINVCERRORS is for in the table SOP60100? I get values in there for linked POP to SOP that seem not to work i.e. (we have received an item on a linked PO, but the committed sales line says nothing was received yet - still says purchased - and the committed quantities now say 0 instead of the sales order quantity like it should say). I'm guessing values in this column/field are to specify what went wrong during receiving to cause this error. Dave ...

How to access siebel data
How to access siebel data from vc++ -- Sivaswami Jeganathan ...

Data validation #23
Hi, I am using the Data Validation feature. I have specified a short list, but cannot select a blank cell entry even though I have the 'ignore blank' box selected?? Any ideas? Don- Press the Delete key to make a "blank" entry. That should not be blocked by Data Validation. -- Jim Rech Excel MVP "Don Niall" <donniall@aol.com> wrote in message news:1a43201c44e38$c68e5bd0$a401280a@phx.gbl... | Hi, | I am using the Data Validation feature. I have specified a | short list, but cannot select a blank cell entry even | though I have the 'ignore blank...

Replace every non empty cell
Hi, I want to replace every cell with is not empty in a column with th word "DEMO". Can someone help me? Thanks, Serg -- serg ----------------------------------------------------------------------- serge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416 View this thread: http://www.excelforum.com/showthread.php?threadid=27555 To fill the blank cells in the used range of a column: Select the column Choose Edit>Go to Click the Special button Select Blanks, click OK Type DEMO Press Ctrl+Enter serge wrote: > I want to replace every cell wit...

Sort a Range #2
Sorry Everyone, I know this one has been on here a dozen times, but I cant find any relating posts. Say I have 2 columns and 4 rows of data such as: A B 1 2800 $2000 2 2700 $1952 3 2940 $1700 4 2852 $2100 I want the next 3 rows to sort this from smallest to largest according to value in column A. I have a fixed # of data points (3 in this example) and would like to use formulas rather than the sort command so that I can have a x-y scatter plot of the sorted data that updates automatically when ever new data...

remove rows that have any cells values strikethrough to a blank Sheet2
Hi, I am looking for help with a code that is able to cut or delete al rows that has any cell values strikethrough in any column in th currently open worksheet, and paste those rows in the blank Sheet2 fro row 2 downwards. Sheet2 must also have the Header names being copie over to Row 1. The open worksheet has cells strikethrough in all the characters, no just some characters. After the rows have been removed, the open worksheet should not contain blank rows in between. The Header names in row 1 must be i tact. Any suggestions is much appreciated. Thanks in advance ------------------...

Comparing data within 2 lists
I have been asked to compare lists from my 2 main systems. They believe that the information of them is not consistent. How would I compare the 2 lists in Excel since there are over 30 thousand records on them. Information: List 1 List 2 Building no. Color Building no. Color Building 1 Blue Building 2 Red Building 2 Red Building 4 Yellow Building 3 Yellow Building 5 Orange Building 4 ...

Matching data from one table and copy them to another
Hi, I have two Excel tables. Table 1 contains list of animals and their description in two columns. Table 2 contains list of animals and results of a test. Not all animals from Table 1 mach the animals in Table 2, but most of them do. I have to link those two tables in a way that test result from table 2 appears in Table 1 Column 3 but of course I must use animal ID column as a reference to match data. How can I do this? It's always a good idea to reveal ranges... That said... Table1 = A2:B100 Table2 = F2:G200 In D2 enter & copy down: =MATCH(A2,$F$2:$F$100,0) In C2 enter &am...

How to combine data from 2 separate workbooks onto 1 worksheet
I am planning a meeting for 100 attendees. Our database contains a unique ID for each attendee as well as their name, mailing address, phone number, etc. I have another database that I received from our Travel Department containing the airline information (arrival date, arrival time, flight number, arrival airport, etc). How can I merge the 2 databases together into 1 database without having to copy/paste each attendee's information individually. Both databases have the unique ID for each attendee - is there a way to have Excel "find" the unique ID and then ad...

eliminating unwanted data
Column a has data, column b has data. I want ap1 to equal q1 if a1 equals b1. Actually I am looking for a way to eliminate rows if data in one column appears in a second column. Thank you Your question is confusing. Do you simply want to delete rows where the value in column A equals value in column B? I don't see the relevance of making ap1 to equal q1 if you are deleting the row anyway where you have 2 equal values. Might as well compare A and B and delete. Anyway the following is a sample of deleting rows based on a condition. It firstly sets the interior ...

Multicurrency entries out of balance
Hi, I ran the "Multi-Currency Debit/Credit Not Matching" automated solution for GP10 and several journal entries were reported as unbalanced, for both open and closed periods in two companies. (See end of posting for sample). The other automated solution, "Trial Balance out of Balance" for functional amounts, came out fine. Additionally: I followed Mariano Gomez's suggestion in a related posting and executed "Maintenance > Checklinks" option for all the tables. Seven pages came out, most of them CM related (journals, distribution, receipts, deposit w...

Highlighting cells if a value is obtained
I have an IF equation and when it fails/brings up a certain value i would like it to highlight the cell as a color, specifically red. Is this possible? If so how? For example. The equation is =IF(G11>130000, "No Bolt Specification For Carbon Steel", "0") In the true instance I need that cell to be highlighted to ensure the operator knows what happened. Thank you for your help Select Format | Conditional Formatting... Formula is =G11>130000 Choose a Format Ok -- HTH... Jim Thomlinson "dippy" wrote: > I have an IF equation ...

Hidden source data
Hi. I wonder.... Why does my graphs go blank when I hide the cells containing the source data? Is there a way to omit this annoying occurrence? -- Hilsen �yvind Granberg Hi, when you go Tools|Options|Charts is there a tick in the "Plot visible cells only" box? If there is then deselect that option. Ken Johnson Thank you, Ken! That did it! I fumbled around a little until I found out that I had to mark an graph prior to follow this procedure. I'd say my spreadsheets looking much nicer when a lot of number collecting cells are hidden -- Hilsen �yvind Granbe...