PDA

View Full Version : Excel macros



camac
27-07-2002, 05:27 PM
In constructing a Macro in excel I want to go to a column of figures, run down that column & paste a new entry in the next empty cell. In other words "End-Down" "Down" "paste". But when I record the macro it does the end-down bit but then specifies the next cell. But each time I run the macro I want it to *add* to the column not just replace the last entry. Help please!!

robo
27-07-2002, 05:41 PM
Maybe relative verysus absolute version of movement? I am sure I have seen this work, end <end> <down> versus <down 28>.
robo.

parry
27-07-2002, 10:05 PM
Hi, edit the macro then after the line that has ...
Selection.End(xlDown).Select
enter this code ...
SendKeys "{down}"


The Selection.End(xlDown).Select moves to the last row with data in the current cloumn and Sendkeys is a way of telling Excel key movements, with "{down}" being the option to move down one row. You will have other code after Selection.End(xlDown).Select so just enter a new line after this and enter the sendkeys statement & keep the remainder of the code (which pastes the data in)

cheers
Parry

robo
27-07-2002, 11:30 PM
Parry
You appear to know your stuff.
I should learn more Excel macro speak, haven't upgraded my V4 macros yet.
robo

parry
28-07-2002, 09:58 AM
Thanks Rob. Still consider myself a beginner at VBA but finding answers for Excel/Access on forums like this is a good way for me to learn more. I always test my code before inflicting on others just to be sure :-)

camac
28-07-2002, 11:41 AM
Thanks but that doesn't quite get there. I want the macro, each time it is run, to build up a column of figures i.e. run down the existing column, step into the next empty cell & "paste". The next time thru , the next empty cell. So the column builds.
(using excel 97)
I used to be able to write this easily back when computers were run by steam but this new elictricity stuff ! duh

parry
28-07-2002, 01:06 PM
OK, sounds like you need a For/Next statement which does this, with the possibility of an If statement as well. You will need some parameters though such as where you are taking data from to paste it in and at what point the macro is to stop (ie when there is no more data to copy from).

I'm not sure I clearly understand what you want. Why repeat copy-paste continually instead of selecting all the data from the original column (or whereever your copying the data from) and straight pasting it in?

I'm a bit slow on Sundays so youll have to explain a bit more :-)

camac
28-07-2002, 01:32 PM
Thank I'm writing a macro that will be used to work on an invoice. the original info will be entered into an invoice template. The macro print the invoice & then takes the various entries from that invoice and adds them to a database. Hence the need to go to the bottom of the column of data, step down 'one' & paste in the new data - each time an invoice is printed. Like I said, it used to be easy before this VB cane along.

Capt Jimbo
28-07-2002, 01:52 PM
The following should get you to the cell you want
Can you "paste" from there?

[i]Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select

parry
28-07-2002, 02:38 PM
Cool CJ. Does exactly what I had posted earlier with the sendkeys statement but offset was a new one to me. Looks a bit more flexible than the crude sendkeys statement.

Camac, do you understand? What this does is go to the last cell which has data then goes down a cell in the same column.

However, it wont work properly if you have empty cells in between. For example if a1-a5 has data, cell a6 is blank then a7-a10 has data, the "End|Downarrow" keystrokes within the macro you recorded will only go down to cell a5, then 1 more cell down will be a6, not the last empty cell in the column which is a11.

Do you know how to edit a macro? We are saying the macro you recorded has code written automatically by Excel and we are just adding a couple of lines in the middle of the code. Thats why I suggested you find the line in the code which has "Selection.End(xlDown).Select" as this line indicates the point you are deciding where to paste the copied cells.

Dont touch the rest of the code. After entering the Senkeys or offset line you will probably have a line "ActiveSheet.Paste". Apologies in advance if Im explaining this at too low a level - it's difficult to understand your experience level :-)

cheers
Parry

camac
28-07-2002, 02:55 PM
Thank y'all for your help. That does the trick
I have in the past "written" a few macros (back when 123 was king) but not lately. In the meantime they have introduced this "record" thing and the VB. Changed the language. Frustrating when you know you can do it but can't find the right word.

again thanks

parry
28-07-2002, 03:34 PM
Cool. Its just a matter of getting used to new keywords I suppose. It's quite good having recorded macros as it writes most of the code for you and you just need to edit it to get around certain things - like moving down one cell :-)

cheers
Parry

robo
29-07-2002, 09:16 AM
I always get nervous with things like invoices in Excel. Cock it up and people lose money, and that can be scary.
Bad enough when using proper invoicing system.
Sorry, the accountant in me can't help it (I mostly keep him locked up).
robo.

Russell D
29-07-2002, 09:37 AM
Use
Range("startcell").End(XlDown)(2)
This will go to the next cell below the last cell.

nana
17-10-2007, 04:38 PM
good day...
i need help on this macro thingy! :help:

i'm importing data from a .txt file that will be in sheet 1. These data will always be changing.

from there, i need to copy dates and figures to sheet 2 with formulas.

1) for a date in sheet 1, say cell C3, how do i create a macro that will paste to sheet 2 cell A6 and go to the next empty cell?

2) and from the next empty cell, repeat point 1 and go to the next empty cell and so on...

Thks

wratterus
17-10-2007, 07:39 PM
Hi nana, and welcome to PressF1. I can't help with your problem, but if you started a new thread, instead of piggybacking on a 5 year old one, you'd have a much greater chance of getting help. ;)

nana
18-10-2007, 01:20 PM
haha.. dont really know how to use forums.. thks wratterus :thumbs:

wratterus
18-10-2007, 02:07 PM
If you want to start a new thread, go to this (http://pressf1.pcworld.co.nz/forumdisplay.php?f=4) page. Click the "New thread" button on the top left. ;)

nana
19-10-2007, 06:16 PM
Thks Wratterus!!!