write macro to input data

I am new at Excel.  I want to write a small macro, four or five columns where 
I can go to column 1 and input data, then go to column 2 and input date, 
etc., then when I get to the end of the fifth column have it return and go to 
add more  data in column 1.   Any help will be appreciated from the experts 
in Excel.  Thanks.
0
MacroHelp (2)
12/28/2005 7:19:02 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
547 Views

Similar Articles

[PageSpeed] 25

one way to do this is to use a worksheet_change event
right click sheet tab>view code>copy/paste this>modify to suit>SAVE

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Row > 5 And Target.Column = 8 Then ActiveCell.Offset(1, -6).Select
End Sub



-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Macro Help" <MacroHelp@discussions.microsoft.com> wrote in message 
news:6192FCD7-E79F-40E9-97FB-E739EF29FAF3@microsoft.com...
>I am new at Excel.  I want to write a small macro, four or five columns 
>where
> I can go to column 1 and input data, then go to column 2 and input date,
> etc., then when I get to the end of the fifth column have it return and go 
> to
> add more  data in column 1.   Any help will be appreciated from the 
> experts
> in Excel.  Thanks. 


0
Don
12/28/2005 8:39:20 PM
This is a basic method, the way I started, before getting into UserForms
etc.
Let's assume, you have some data in columns A thru E (5 columns).

Code:
--------------------
    Sub NewData()
  Dim Config As Integer
  Dim Ans As Integer
  Application.ScreenUpdating=False
  Range("A1").End(xlDown).Select
  'This inserts a value of 1 for tabulation. Use Only if you want to'
  ActiveCell.Value=(1)
  ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
  ActiveCell.Value=InputBox("Enter Your Data")
  ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
  'Inserts the current date'
  ActiveCell.Value=Now
  ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
  ActiveCell.Value=InputBox("Enter Your Data")
  ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
  ActiveCell.Value=InputBox("Enter Your Data")
  ActiveCell.Offset(rowOffset:=0,columnOffset:=1).Activate
  ActiveCell.Value=InputBox("Enter Your Data")
  'User is prompted to continue or quit'
  Ans=MsgBox("Do You Have Additional Entries?", vbYesNo)
  If Ans=vbYes Then
  Application.Run("NewData")
  End If
  'Cursor is returned to the Home Cell of A1'
  Application.Range("A1").Select
  End Sub
  
--------------------


Hope this helps you some. I know VBA can be a little initimidating when
you first get into it.
Good Luck in your efforts.


-- 
VBAvirgin
------------------------------------------------------------------------
VBAvirgin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16327
View this thread: http://www.excelforum.com/showthread.php?threadid=496474

0
12/29/2005 10:50:45 PM
How about not using a macro?

Data|form might be enough???

And if you want to try creating your own userform, Debra Dalgleish has some
getstarted instructions at:
http://contextures.com/xlUserForm01.html

Macro Help wrote:
> 
> I am new at Excel.  I want to write a small macro, four or five columns where
> I can go to column 1 and input data, then go to column 2 and input date,
> etc., then when I get to the end of the fifth column have it return and go to
> add more  data in column 1.   Any help will be appreciated from the experts
> in Excel.  Thanks.

-- 

Dave Peterson
0
petersod (12004)
12/29/2005 11:48:42 PM
_I_whole-heartedly_agree_ with D. Peterson's comments. Userforms are
definitely the neatest and cleanest way to go.

I was just imagining, from your initial question...(asking for a
macro)...that you may be familiar with some older or different programs
and the macros used within them, such as Lotus 123, or CA SuperCalc, or
QuattroPro (which is what I used last, up until September of 2004). 
I'm by no means a programmer of any sorts, but I have now created
userforms, along with my macros, and I'm at a fairly comfortable level,
although I'll be the first to admit there's so much farther to go, but
my point is, the learning curve is only as difficult as you make it, so
hang in there.
It will take some time getting your head around VBA, but I'm sure
you'll do it.

A big help to me has been: "Excel VBA Programming for Dummies"...John
Walkenbach. Wiley Publishing.
That...and the help, such as I have received, from the experts on this
forum.
Good Luck.


-- 
VBAvirgin
------------------------------------------------------------------------
VBAvirgin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16327
View this thread: http://www.excelforum.com/showthread.php?threadid=496474

0
12/30/2005 12:10:08 AM
Reply:

Similar Artilces:

Help: Macro that runs various if statements, inserting rows etc
I am a newcomer to Macros and excel programming and was hoping someone could assist / provide the code to do the following: I have a +50,000 line spreadsheet, that needs to be formatted. Given the size I would like to automate this. There are 3 key formatting changes required and detailed below: n = 1 1. If Cell A(n) not equal Cell A(n+1) then insert a new row below A(n), make Cell F(insertedrow)=1, make Cell G(insertedrow)=A(n+1), make Cell H(insertedrow)=B(n+1) 2. If Cell C(n) not equal Cell C(n+1) then insert a new row below C(n), make Cell F(insertedrow)=2, make Cell G(inserte...

Import data from DBF files
What's the best way to import a large dbf file into Access (records from a DBase file)? I have one dbf that has over 220,000 records in it. It gets to about 75% and says "The search key was not found in any record." Everything is patched to the max (Windows, Office, Jet, etc). This doesn't happen with any other dbf file, but they are all smaller. Also, oddly, I can copy and paste 50,000 records in a shot and paste them through a query, but I don't want to keep doing that for every large dbf I have. The Access tables have no problem holding the data, just transf...

Macro Help- combining "CS" files
Below is the macro I have to go to a certain file and combine all spreadsheets. I did not write this macro myself. I just received it and modified it to work for my situation. When this maco is run it gets to the first file and says I cannot change a read only file and says I must unprotect the worksheet. This sheet is not protected but I really only want to copy the info on it anyway. Is there a way to modify this macro to copy the information. I could save all of the "CS" files as new files but that would defeat the purpose of automating this job Any help is greatly a...

Is there a way for Windows Service to Read/Write HKCU Reg key ?
Hi: I'm writing a Windows Service program which needs to read/write the HKEY_CURRENT_USER registry key for the current logged on user. I know that for a Windows service does not have concept of HKCU since it is always running in SYSTEM's context. But is there a way (trick?) for a service to access the HKCU registry key? Thanks In Advance ! Polaris Hello Try, from a user application (the one that is logged on, or the current user), to send the services the user's SID. Then access the HKCU through HKEY_USERS\users_sid\............ Hope that helps, Elias "Polaris&qu...

Deleting shortcut key In Recording new Macros dialog box
I have recorded several new Macros and I am running out of shortcut key letters to put into the shortcut key box within the Record a new Macro dialog box. How could I delete the shortcut keys that I have already used to reuse again to record new Macros? One way: Choose Tools/Macro/Macros... Select a macro for which you want to delete the shortcut. Click Options... Clear the shortcut key input box. Click OK, then Cancel. In article <9DC795B4-1EBE-4685-94DF-CD79ABEAF45D@microsoft.com>, John <John@discussions.microsoft.com> wrote: > I have recorded several new Macros ...

Pivot Table Help
I have a column for the actual date and a column for a grade. I hav created a pivot table to total up information in regards to this data. I need to sum up the amounts by the month not the individual day. However I need to keep the individual date as mm/dd/yy. Is there a way that I can manipulate the pivot table to sum up based o the month only? Date 1/2/04 1/3/04 ... 5/6/04 Grade 2 1 ... 5 The pivot table works ok however I really only need to sum up th totals for each month not for each particular day. Is there a way tha I can use the same day column but only calculate the totals...

Write
I am trying to change the default margins. I am requested to use the Write mode to do this. What is Write, and how do I access it? Are you using voice recognition? Margins are simply changed in the Arrange Menu in Publisher. Are you in the right newsgroup? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "CBC" <anonymous@discussions.microsoft.com> wrote in message news:183bb01c44a40$107bd5d0$a501280a@phx.gbl... > I am trying to change the default margins. I am requested > to use the Write mode to do this. W...

Excel Macro #2
I need to create an Excel Macro tha will import a series of txt files, one below the other, automatically. The macro will be run every two weeks and each txt file will have additional rows. What I want to know is how to automatically position the curson to the next open cell in column A after the previous txt file was imported. Can anyone help? Range("A1").Offset(ActiveSheet.UsedRange.Rows.Count, 0).Select Dan E "Robert Blankenhorn" <Blankenhorn.B@cpcnet.co.richland.oh.us> wrote in message news:05ab01c35dc3$99bb7130$a001280a@phx.gbl... > I need to creat...

uploading data for storage
As a beginner in Microsoft Money 2000, how does one upload data to microsoft for a backup? (As an additional means to downloading data for backup) Thanks, Greg No version of Money supports uploading data to Microsoft for backup purposes. If an offsite backup is of interest to you, I would suggest that you try Google with the terms "offsite backup". -- "Greg M" <gmrachek@ameritech.net> wrote in message news:085001c370ba$b1d1f1e0$a001280a@phx.gbl... > As a beginner in Microsoft Money 2000, how does one > upload data to microsoft for a backup? (As an additio...

Quick way to expand all columns to show all data?
I often open newly-created spreadsheets where the data in columns is wider and taller than it displays initially. I then drag select all the column headers and double-click between the first and second column header. That expands the width of all column displays to show all the data, widthwise. I then drag select all the row headers and double-click between the first and second row header. That expands the height to show all data. Phew. Is there an easier way to do this? David, Click the box above and to the left of cell A1, and that will select all the columns and rows - ...

Writing formula for excel worksheet
Item Rate1 Rate2 Rate3 A 3 4 6 B 8 5 23 C 56 5 78 Above is my excel sheet. I want to write some formula so that in every row cell having minimum rate should come bold. Like For Item A - Rate1(3) for Item B - Rate2(5) For Item C - Rate2 (5) There may be a neater way but this works. Somewhere in you sheet in a cell type =min(A1:a4), I used L1 Change this to watever range you are trying to evaluate. Then select this range and click format - conditional format - cell value - is equal to =($L$1) and apply your bold font. The lowest value in the range will then become bold. Mike "Gaur...

Keyboard Macro Just Copies Content of Previous Cell
I created a simple keyboard macro to edit the contents of a cell by simply inserting a "b" in a particular position. For example, I have a series of alphanumeric numbers that start with S1253, e.g., S12536675. I wanted to insert a "b" after the first five digits on the left to revise the series of alphanumeric numbers to start with S1253b, e.g..S1253b6675. However, after creating the keyboard macro, when I run the macro on the next cell to be edited (i.e., S12536676), the macro simply copies the contents of the original cell that I edited when I first created the ...

write protect/write redirect/kios mode
Hey, I LOVE snapshots in virtualmachines and think it would be awesome to have something like this for my main windows install. Anyone know of any hardware/software to redirect writes to another drive/file/memory/hardware? Or hardware to write protect a drive for kios's (i found some once befor the reverted all changes on shutdown, but cant find it anymore ><) I know VSS(virtual shadow copy service) has a function to redirect on write and wondering if i might be able to use that :/ It will just make everything cleaner, and give alot of flexiblity to setting up ...

copy vertical data to horizontaly
Hi, I have a list of data verticaly, which continues to be as follows Name address phone # city Postal code I want them to put in horizontal order every five records to be on one row, so the following names, etc.. be on the 2nd row and so on... I appreciate your help Thanks Hi if this data is on sheet1 starting in cell A1 put the following formula on your second sheet in cell A1: =OFFSET('sheet1'!$A$1,(ROW()-1)*5+COLUMN()-1,0) now copy this formula to the right and down. Afterwards select your data range, copy the data and insert it again with 'Edit - Paste Special - Valu...

Retrieve data from Access
I have a requirement to import data from MSAccess to Excel. I have done this using VB. The problem is that I use a button on the spreadsheet to run the macro. I want to run the macro after I enter data in a cell and press the enter key. Can this be done and how. Thanks in advance ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ try using the SelectionChange event for the relevant worksheet. Call the macro through here, instead of with the button OnClick event. Edwar...

Slow down SendKeys macro
Hello, I have a macro that sends several sendkey commands to modify some settings within a printer's print settings dialog box. The problem is that the macro seems to be too fast for the application at times, and the keys are sent before the application is at the place it needs to be. If I print the macro and key the sequence in by hand it works perfectly. I would like to add a quarter second delay between all the keys that are pressed in the sequence if possible. I've examined some loop commands, but they have resulted in much more agony than help. Any idea on how to keep the macro...

Import Data From Landscape Format
I have a data set from an old Pascal program that was exported into this fomat: ID # : 10106 Sex : Female Raw Scores : 1 4 1 0 3 6 3 T Scores : 53 114 65 43 64 83 42 I need the headers to be ID#, Sex, Raw Scores 1-7 and T Scores 1-7 and then to pick up the data and place it in the appropriate cell. Is there a script that will accomplish this? Thank you for your help!! ...

Cannot write to memory location
I can not (all of the sudden) run Publisher in the last week. When starting to run it, I get that it cannot write to a certain memory locaiton, so it is shutting down. I need to get this program working. I have read through this forum and tried the different fixes as well as the knowledge base (emptied the temp file, disabled anti-virus, etc.) but it still won't work. An example is: "The instruction at "0x77f585c0" referenced memory at "0x00000000". the memory could not be "written". Click on OK to terminate the program." The header read...

enable Macro
I have a word document with several macros . I have to enable them every time i use the word doc is there a way i can stop the enable pop up without lowering the security settings. any help would be great thanks phill --- Message posted from http://www.ExcelForum.com/ Phil This is an Excel News Group, but check out Help in Word under Answer Wizard and "certificates". Check out "create self-signed certificates" using the SELFCERT feature of MS Office. Same for Excel and Word. Gord Dibben Excel MVP On Mon, 23 Aug 2004 18:45:36 -0500, philthedil <<philthedil.1b...

Macro to move file
Is there a way to use a macro in a workbook to move a file from one folder to another folder by copying it? I tried to record a macro to do it but nothing shows up in the VB editor. Thanks Dave Hi Dave Try the following? Sub SaveCopy() ChDir "C:\Documents and Settings\{Enter new Directory name here}" ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\{Enter new directory and file name here}, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False End Sub This is th...

Data Validation list boxes
Hi-- I have three columns which have data validation list boxes attached, so = that=20 when you click on a cell a little handle appears and you select a name = from=20 the list box that appears. In one of the columns, however (and always = the=20 same one), the handle doesn't show up. When you to type in a cell, the=20 warning box still appears saying that you have to select data from the = list,=20 but the handle never appears, making selection impossible.=20 Thinking the workbook was corrupt, I tried copying the whole sheet into = a=20 new workbook, and the validation in the problem col...

Interpreting Data
I am trying to convert some data into percentages in an excel spreadsheet. For example, the spreadsheet asks a question, the data is good, fair, excellent and about right. I am trying to convert those answers into percentages. Does anyone know how I can do this? Any help would be appreciated. Thanks! One way would be to list all the responses, then calculate that W% said fair, X% said about right, Y% said good, and Z% said wicked ill (I have teenagers in the house). This makes a pie chart or stacked column or bar chart. Here's an easy way to tabulate and compute the percenti...

Data migration and relationship question
Hi I am wanting to migrate data using the DMF from our bespoke system into CRM. We have 2 tables which contain Company incl Address and People. The first table contains multiple entries for the same company only with different addresses for the various sites. The People table has contact records that relate to individual company sites. I believe I'm wanting to create records in the DMF database in the following tables. Account, Contact, CustomerAddresses. In CRM I want to show that each address is related to both the Account & Contact but it seems that I have to make a choi...

how do i write checkbox to.....
This is a multi-part message in MIME format. --------------050607000103070200020407 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit how do i write checkbox1 boolean to xml in vb6 i can only do cstr( checkbox1.value) but in vb.net can't write to xml. i am doing irc chat similar to mirc or pirch chat. rw.WriteConfigInfo("User Modes", chkInvisible.Text, chkInvisible.Checked, "PirateChat.xml") in procedure events: ' Both the Key and the Value are filled ' Find the key ...

static data series height after data insertion
How do I add a cell to a data series without changing the range of the data series? I have a line chart with a line that represents only 30 days of data (the chart data series is defined to be exactly 30 cells high). When I update the data with today's data by inserting today's date and data to the top of the data series range, the data series definition in the charts changes to 31 days high. I need it to be only 30 days high after the insert. I don't want to change or write a macro to change the data series definition in the chart. I tried naming the range and using the name ...