View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Joe M. Joe M. is offline
external usenet poster
 
Posts: 97
Default Need macro to move to first blank cell in column

Thanks, but extending a formula down will not help for what I need. I need a
macro as this worksbook is to automatically fill the values. Also, I am not
copying down from A2 to A3 to A4 etc. I am copying A1 to i.e. A43. B43 is
copied from another w/s fixed cell location. C43 is filled by a formula
extended from C42. When the macro is run again the next row; A44, B44 and C44
are to be filled. I hope this better explains what I am trying to do.
Thanks,
Joe M.

"Rik_UK" wrote:

If the description of what you are doing is all you would like to achieve,
then this could be acheived by formulae in each cell.

From your description:
cell A1 is copied down column A of the same sheet
cell B2 and subsequent cells in column B are the same cell reference from
another sheet (assume named 'sheet2' and cell ref is B1)
cell C1 is copied down column C of the same sheet.

If you put =$A$1 in cell A2; ='sheet'!$B$1 in cell B2; =C1 in cell C2; and
and the select/highlight the three cells A2,B2,C2 together and then click and
drag down the formulas in the columns you will get the result as described,
without the use of macros.

The reason that the recorded macro doesent work though, is that it is
'static'. Unfortunately Excel is unable to detirmin when an incremental macro
is required, for example to step through rows. The macro would need to be
viewed in the VB editor and edited to allow the row counts to increment, or
for the last blank cell to be identified and that reference used each time
the maro is run.

Not an ideal answer i know, but i hope it helps...


--
Kind regards

Rik


"Joe M." wrote:

Hi. I am trying to use a macro to move from A1 to the first blank cell in col
A. There are no gaps in the data. When the first blank cell is selected, data
will be copied and pasted from another worksheet. Then the adjacent cell in
col B must be selected and again data will be copied and pasted there from
another worksheet. Finally, the adjacent cell in col C must be selected but
the formula from the cell above must be extended down one cell. I have
recorded a macro which does all this but the problem is that when I repeat
the macro the same row is always selected instead of the next blank one. Here
are the steps I did while recording the macro:
1 .Select & copy A1
2.Press <cntl + <end (this brings me to last filled cell in col C)
3. Press down arrow key 1 time and left arrow key 2 times to move to 1st
blank cell in col A.
4. Paste in col A blank cell
5. Copy cell from other worksheet (always the same cell #)
6. Select original worksheet. Press right arrow key once to move to col B.
7. Paste in col B blank cell
8. Press right arrow once and up arrow once to move to last filled cell in
col C.
9. Extend the formula down one cell in col C.
Now the previous 3 blank cells in col A, B & C are filled. But when I
repeat the macro the same cells are filled, not one down each time. Can
someone help?

Thanks,
Joe M.