| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 22681 | 2002-07-27 05:27:00 | Excel macros | camac (1228) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 65950 | 2002-07-27 05:27:00 | 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!! | camac (1228) | ||
| 65951 | 2002-07-27 05:41:00 | Maybe relative verysus absolute version of movement? I am sure I have seen this work, end <end> <down> versus <down 28>. robo. |
robo (205) | ||
| 65952 | 2002-07-27 10:05:00 | 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 |
parry (27) | ||
| 65953 | 2002-07-27 11:30:00 | Parry You appear to know your stuff. I should learn more Excel macro speak, haven't upgraded my V4 macros yet. robo |
robo (205) | ||
| 65954 | 2002-07-27 21:58:00 | 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 :-) | parry (27) | ||
| 65955 | 2002-07-27 23:41:00 | 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 |
camac (1228) | ||
| 65956 | 2002-07-28 01:06:00 | 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 :-) |
parry (27) | ||
| 65957 | 2002-07-28 01:32:00 | 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. | camac (1228) | ||
| 65958 | 2002-07-28 01:52:00 | 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 |
Capt Jimbo (17) | ||
| 65959 | 2002-07-28 02:38:00 | 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 |
parry (27) | ||
| 1 2 | |||||