How to get a macro to do what i want?

I have designed a form in Excel and the data that is inputted i want to be 
able to save in a separate worksheet.  The problem i am having is that 
eveytime i record the macro, it over writes the same row each time (if that 
makes sense).  For example if i copied every cell in the form and paste them 
into the database Row 1, i then want the new piece of information inputted 
into the form to go into Row 2 and then Row 3 etc....  But it keeps going 
into Row 1, it wouldnt move down the rows.  

Hopefully this makes sense to someone and someone can give me some advise.
0
Carrie (48)
4/28/2005 10:54:03 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
423 Views

Similar Articles

[PageSpeed] 16

Calculate the last row

    iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
    if iLastRow <> 1 Or Range("A1")<>"" Then
        iLastRow = iLastRow + 1
    End If

then use this in the data placement

    Cells(iLastRow,"A").Value = myValue

etc.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Carrie" <Carrie@discussions.microsoft.com> wrote in message
news:1CEBC706-AA6D-4C23-ADD2-AEB87A5A2D00@microsoft.com...
> I have designed a form in Excel and the data that is inputted i want to be
> able to save in a separate worksheet.  The problem i am having is that
> eveytime i record the macro, it over writes the same row each time (if
that
> makes sense).  For example if i copied every cell in the form and paste
them
> into the database Row 1, i then want the new piece of information inputted
> into the form to go into Row 2 and then Row 3 etc....  But it keeps going
> into Row 1, it wouldnt move down the rows.
>
> Hopefully this makes sense to someone and someone can give me some advise.


0
bob.phillips1 (6510)
4/28/2005 11:06:18 AM
Thank you for that information but i do not understand the last bit about 
Data Placement.  Do i need to write that within the marco? If so then is it 
underneath the End If?

Also you wrote calculate the last row, do i need to write that into the macro?

Sorry about being a pain!

Carrie

"Bob Phillips" wrote:

> Calculate the last row
> 
>     iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
>     if iLastRow <> 1 Or Range("A1")<>"" Then
>         iLastRow = iLastRow + 1
>     End If
> 
> then use this in the data placement
> 
>     Cells(iLastRow,"A").Value = myValue
> 
> etc.
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "Carrie" <Carrie@discussions.microsoft.com> wrote in message
> news:1CEBC706-AA6D-4C23-ADD2-AEB87A5A2D00@microsoft.com...
> > I have designed a form in Excel and the data that is inputted i want to be
> > able to save in a separate worksheet.  The problem i am having is that
> > eveytime i record the macro, it over writes the same row each time (if
> that
> > makes sense).  For example if i copied every cell in the form and paste
> them
> > into the database Row 1, i then want the new piece of information inputted
> > into the form to go into Row 2 and then Row 3 etc....  But it keeps going
> > into Row 1, it wouldnt move down the rows.
> >
> > Hopefully this makes sense to someone and someone can give me some advise.
> 
> 
> 
0
Carrie (48)
4/28/2005 11:36:05 AM
The first bit shows you have to calculate the next free row by identifying
the last filled row. Somewhere you mast have some code that copies the data
to a worksheet. I haven't seen the code, but maybe something like

    Range("A1").Select
    Selection.Formula = "ABC"

where ABC may be a variable, or another range, or just a string. I am
suggesting replacing that with

    Range("A" & iLastRow).Value = "ABC"

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Carrie" <Carrie@discussions.microsoft.com> wrote in message
news:51F7E540-E73B-4012-BAD7-5B48EFB148CF@microsoft.com...
> Thank you for that information but i do not understand the last bit about
> Data Placement.  Do i need to write that within the marco? If so then is
it
> underneath the End If?
>
> Also you wrote calculate the last row, do i need to write that into the
macro?
>
> Sorry about being a pain!
>
> Carrie
>
> "Bob Phillips" wrote:
>
> > Calculate the last row
> >
> >     iLastRow = Cells(Rows.Count,"A").End(xlUp).Row
> >     if iLastRow <> 1 Or Range("A1")<>"" Then
> >         iLastRow = iLastRow + 1
> >     End If
> >
> > then use this in the data placement
> >
> >     Cells(iLastRow,"A").Value = myValue
> >
> > etc.
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Carrie" <Carrie@discussions.microsoft.com> wrote in message
> > news:1CEBC706-AA6D-4C23-ADD2-AEB87A5A2D00@microsoft.com...
> > > I have designed a form in Excel and the data that is inputted i want
to be
> > > able to save in a separate worksheet.  The problem i am having is that
> > > eveytime i record the macro, it over writes the same row each time (if
> > that
> > > makes sense).  For example if i copied every cell in the form and
paste
> > them
> > > into the database Row 1, i then want the new piece of information
inputted
> > > into the form to go into Row 2 and then Row 3 etc....  But it keeps
going
> > > into Row 1, it wouldnt move down the rows.
> > >
> > > Hopefully this makes sense to someone and someone can give me some
advise.
> >
> >
> >


0
bob.phillips1 (6510)
4/28/2005 12:06:39 PM
Reply:

Similar Artilces:

spell check does not visually cycle through cells when run from macro?
I'm trying to recreate the same behavior in a macro that spellcheck has when the spellcheck button is pressed. The spellcheck does not cycle through the cells visually when run from a macro, so when there is a misspelled word, you cannot see the cell it is a part of. If anyone knows how I can get this behavior to work, it would be GREATLY appreciated. I did a record new macro, and got code similar to the following: Sub testSpelling() Range("F2:F500").Select Selection.CheckSpelling SpellLang:=1033 End Sub thanks, dan Dan, Add the fiollowing to your code as shown: ...

Setting directory for file save in macro
Hi Thanks to a generous poster I now have a great bit of code to use in a macro for Excel which saves a text file (I'm no VBA programmer so this was really helpful), i.e.: Open "Test.txt" For Output As #1 (rest of code) My problem now is this: The macro saves test.txt to Excel's current active folder, rather than the folder that the current workbook is contained in. For example, if I last saved an Excel workbook to c:\workbooks\, and the workbook that I have open exists in c:\workbooks\workbook1\, when I run the macro it saves the text file in c:\workbooks\. If I ...

How do I get automatic completion to work for an in-cell dropdown
If I have a list of Alpha, Beta, Gamma and an in-cell dropdown, I'd like typing 'G' to auto-select Gamma. I'm pretty sure it was doing that at some point, but now, it does not. This isn't supported under data|validation. You could use A,B,G in the dropdown and then use a helper cell with a formula in it, though. Say you put your abbreviations in A1:A3 of sheet2 and the real names in B1:B3, then you could use: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) Joseph Weisblatt wrote: > > If I have a list of Alpha, Beta, Gamma and an in-cell dropd...

getting the error when updating file xls file.
Hi. We are using exchange 2003 server and office XP but know we start to Upgrade our office to office 2003 Know we are face problem. When our user try to make changes to file in public folder there can not make the changes it say “file is read only” but that user can make the changes or edit that same file from the P.C were office XP is installed. Do I have to install some patch if yes can you please send that patch because in trying from last two day and I can not find it. Note: If possible can you reply on parag.lopes@madinatjumeirah.com Just a guess... Are you s...

Comments entered on Task Updates to Manager: Where does it get sto
There is a checkbox option on the My Tasks page for team members to select and send comments about task updates to their managers. Anyone know where these task comments can be reviewed other than when the PM opens it up to approve? It does not seem to be retained anywhere like the Notes get retained. Hi Kahuna, PM can see the comments with Approvals -> Task Updates -> Go To -> Applied Requests and Errors, clicking on the task name. The approval history is listed there including comments. A team member can see them with clicking in the task name in 'My Tasks&#...

Can you record a macro in Publisher like you can in Excel?
In Excel I use the feature to Record a Macro. Excel records the keystrokes and converts to VB. Does Publisher have that option? In my version, Publisher 2003, I can Create a Macro, and it takes me into VB. However, I don't know how to code VB. Darlene wrote: > In Excel I use the feature to Record a Macro. Excel records the keystrokes > and converts to VB. Does Publisher have that option? In my version, > Publisher 2003, I can Create a Macro, and it takes me into VB. However, I > don't know how to code VB. Publisher does not have a Macro Recorder, sorry. --...

MFC get drive information
Dear all, How to get drive information of my computer? I want to get available driver letters and the corresponding disk space information. Thanks! Best Regards, Crystal Use GetLogicalDrives() to get drive informatio. Use GetDiskFreeSpace(... to get drive space. Sekhar Hyderabad. "Crystal" wrote: > Dear all, > > How to get drive information of my computer? > > I want to get available driver letters and the corresponding disk space > information. > > Thanks! > > Best Regards, > Crystal > > > > > You can use cha...

Macros/Signatures Question
I have an Excel Workbook that I created for a PC Order Form. I set it up to only make certain rows visible based on the Machine Model that they choose. I digital signed the Worksheets and exported the Digital Signature Certificate and installed it on the User’s PCs. The code works great. The user opens the Workbook and it runs the Macros with no problem. The problem I have is when the user goes to save the Workbook. They get an error that “Excel can not sign VBA macros when saving to this file format. Do you want to remove the digital signature and continue saving this workbook?”...

Macro Copy and Paste
I am currently trying to copy from one workbook that has 7 differen tabs and paste into one worksheet on a separate master workbook. M problem is that when I set up the macro to copy from one of the tabs i the workbook and paste to the other work book, I only want the the row to come in where there is data. Currently I am having to run a macr that takes every 100 lines, but ideally I only want the informatio copy and pasted where there is only data. In addition, once I have copy and pasted everything from the first tab my next step is to copy and paste information from the second tab t th...

Referring to a FileName in Macro
I have a Macro with the following code Application.Run "TestQry!Macro2" Application.Run "TestQry!Macro1" TestQry is the name of the file. Is there anyway that this macro cod can refer to the activeWorkbook or filename so that if the file i saved to a new name this macro will still ru -- Message posted from http://www.ExcelForum.com How about just: macro2 or even call macro2 "DoctorV <" wrote: > > I have a Macro with the following code > > Application.Run "TestQry!Macro2" > Application.Run "TestQry!Macro1" > &g...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Why can't i get my wanted notes by XPath
Need a hand to help me!!!!!Support for XPath in MsXML6.0,I can't get my needed notes by XPath Environment:XPP SP3,English Tool:Autoit3 with _XMLDomWrapper.au3 Target:From unattend.xml(when installing XP or Vista, you 'll use), delete /unattend/settings [pass="oobeSystem"]/Component[name="Microsoft-Windows-Shell-Setup"]/OOBE . Unattend.xml: [color="#0000FF"] <?xml version="1.0" encoding="utf-8"?> <unattend xmlns="urn:schemas-microsoft-com:unattend"> <servicing></servicing> <settings pass...

Macro Question #10
Hi, I am working with a text file that I imported into Excel. It is a transactional report of sales by customer. The data will have the customer number and name spread out over three cells. What I want to do is to create a macro that will contatenate all three cell and then perform a paste special and paste them into another cell in another column. Since the number of transactions by customer can vary the customer name and number can appear anywhere on the page. I created a macro to do this using the first customer which appears in cell B5 through D5. The next customer appears...

How do I get outgoing messages to send from a particular email ac.
I have multiple email accounts set up in Microsoft Office. Whenever I compose new mail, it is outgoing from one of them, but as I rarely use that internet service anymore, I want it to come from another one. How do I change what email address is displayed on my outgoing messages? State your Outlook version and, if 98 or 2000, the mode (help->about->second line should say.) --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without read...

I want to design both side of a post card using publisher 03
I have go side one completed. How do I do the other side??? Insert Page -- Don Vancouver, USA "bettboop" <bettboop@discussions.microsoft.com> wrote in message news:68D78C55-BB03-44EC-97AF-D8FBA74FB95C@microsoft.com... >I have go side one completed. How do I do the other side??? ...

Spreadsheet macro stopped working!
Hi, I recently made an Excel spreadsheet for keeping track of my golf score. The spreadsheet is quite simple. It consists of 4 sheets: Scorecard, statistics, database and equations (for calculating some specific). I insert informations about the golf course I'm playing and how my score was etc. I then hit a button "Save scorecard" that runs a macro. The macro inserts specifik values from the scorecard into the database sheet (which works like a charm). The macro also updates the "Statistics" sheet with information about stroke number and points for ...

How do I get a chart to display backwards/forwards?
I have stock data. Row 35 is the nearest day. Row 36 is the next day, etc. When I chart the data using a 2 axes chart (custom), the chart doesn't display backwards. The nearest day displays on the left side of the chart, not the right. How do I change that? Double click on the axis, select the scale tab, and check "Values in reverse order" Jerry Will wrote: > I have stock data. Row 35 is the nearest day. Row 36 is the next day, etc. > When I chart the data using a 2 axes chart (custom), the chart doesn't > display backwards. The nearest day displays on the ...

Excel 2007 macro compatability
I have created a macro in Excel 2007 that includes importing a text file. It works fine on my PC, but fails on a co-workers PC running Excel 2000. The failure points to the "TextFile Platform" command in the macro. I have tried several (437, 1252, 20127), but get the same results. Any solution? Thanks you. Have you try xlWindows -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Al @ Frontier" <Al @ Frontier@discussions.microsoft.com> wrote in message news:3F601F80-F099-42C8-AA18-6D31A85946F9@microsoft.com... >I have cr...

Excel E-mail Macros
I need to know how to create a macro that will send an excel file as an attachment to one individual, and then send one of the file's worksheets to 3 e-mail addresses using the mail recipient method so that the worksheet appears in the body of the e-mail. The e-mail program I am using is Outlook. Any help with this will be GREATLY appreciatd. Hi John, I'd tell you, but Ron de Bruin already did all the work. It'd be silly for me to do it again...Try Ron's site: http://www.rondebruin.nl/sendmail.htm tim "John" <jbegley@dcas.nyc.gov> wrote in message ne...

format macro
Last week, Art was trying to help me with this, but the solution doesn't work for me. I'm looking for a macro that would be contained within my personal.xls, that I could use as needed. Lets say I have A1:C5 selected, which represent headdings and data for jan, feb and march. [On a different spreadsheet, it may be different columns, or a different number of columns. The point is that I will select the range before I run the macro.] I want to do an auto fit for each column, so that the column widths are big enough to fit the numbers. No problem, format-->column-->autof...

Not getting the data output I want
Hello, I need help with my database. Here's the basic layout: I have a table for patient data that is imported on a daily basis. That data is then queried to eliminate null values and the query is the source for my main form. I then have a subform based on a table with procedures that has no data in it. It is strictly for data entry to include the date of service and checkboxes for procedures rendered. I used the PtID (auto number) as my primary key in my main form and the same name for a foreign key in the subform. Those fields are used as the link parent/child fields. I ...

get speed between device incorrect in Win7 with two controller
when I debug a 1394B image device driver on windows 7 x64, I found the result of REQUEST_GET_SPEED_BETWEEN_DEVICES requesting incorrect sometimes, I have two controller, one 1394B (TI's PCIE chip), one is 1394A, my image device run at S800, and connected on 1394B port. I want to get the speed between device and host is 8 (S800), but sometimes I got 4 when I power on my device with cable had connected. Is there a bug of windows 7 1394 bus stack? thanks for any helps. -- Steven ...

I can't get Outlook to synchronize with my yahoo email account.
I keep getting error messages. IS there someone or a website with good directions that might be able to assist me. Thanks! "Dan" <Dan@discussions.microsoft.com> wrote in message news:933B689D-59DF-4237-958C-45A246982C33@microsoft.com > I keep getting error messages. IS there someone or a website with > good directions that might be able to assist me. > > Thanks! Do you have a paid-for premium account with Yaoo? (Unless you're in the UK.......) Dan <Dan@discussions.microsoft.com> wrote: > I keep getting error messages. And those messages are? ...

Not getting emails
Hi, Suddenly, I stopped getting emails to my Windows Mail account. I go through Verizon to get my emails. When I signed on to my verizon account, the email was there and I forwarded it to myself and I did get that one. But I'm not getting any others. Can you help me? Thanks, ...

Macro #17
Does anyone have a Macro to edit a cell to add "=" sign at the beginning of the data Thanks, Alexandra can you be more pacific ActiveCell.FormulaR1C1 = "=" Range(" ").Select Alexandra wrote: > > Does anyone have a Macro to edit a cell to add "=" sign at the beginning of the data? > > Thanks, Alexandra ...