Macro help - copy a cell down
 

I'm trying to do something simple, but it's not working, any help would
be appreciated.

I want a macro which will enter a formula into each cell of a table
column, for the range from the top of the column thru to the bottom of
the existing table. I have tried recording a macro to do this. The
macro enters the formula into the first cell, then copies it and pastes
to the rest of the column. In order to address the potential that a
table may have only one entry, I jump to the bottom of the spreadsheet,
then back up to the bottom of the data, then select the range from there
through the top of the data, less the top cell (so that I don't copy
into the cell above the table when there's just one entry.) Issue is
that I can't get the macro to correctly select the range, it seems to
skip the "step-down" instruction to avoid the top cell when I record
the macro. See below:

Sub HosedUp()
'
' Macro16 Macro
' Macro recorded 2005-09-02 by G
'
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-7, 0).Range("A1:A8").Select
ActiveCell.Activate
ActiveSheet.Paste
End Sub

Not sure what I'm doing wrong, but any suggestions would be welcomed.

---Glenn


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=401593

Can you pick out a column that will always have data in it for every row that's
used?

If you can, maybe something like this would help:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("c1:c" & lastrow).formula = "yourformulahere"
end with

I used column A to get the last row of the table. I use column C to hold the
formula.


gjcase wrote:

I'm trying to do something simple, but it's not working, any help would
be appreciated.

I want a macro which will enter a formula into each cell of a table
column, for the range from the top of the column thru to the bottom of
the existing table. I have tried recording a macro to do this. The
macro enters the formula into the first cell, then copies it and pastes
to the rest of the column. In order to address the potential that a
table may have only one entry, I jump to the bottom of the spreadsheet,
then back up to the bottom of the data, then select the range from there
through the top of the data, less the top cell (so that I don't copy
into the cell above the table when there's just one entry.) Issue is
that I can't get the macro to correctly select the range, it seems to
skip the "step-down" instruction to avoid the top cell when I record
the macro. See below:

Sub HosedUp()
'
' Macro16 Macro
' Macro recorded 2005-09-02 by G
'
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(-7, 0).Range("A1:A8").Select
ActiveCell.Activate
ActiveSheet.Paste
End Sub

Not sure what I'm doing wrong, but any suggestions would be welcomed.

---Glenn

--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=401593

--

Dave Peterson

Dave:

Thanks, that does just what I'm after, except I wanted to start at row
2 vs row 1. Changing the range statement fixed that. I appreciate the
help.

BTW, can you recommend a good book for learning excel macro
programming? Right now, I'm just bumbling around tweaking things I've
recorded, buit I'd like to get into it more.

---Glenn


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=401593

Debra Dalgleish has a list at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. See if you can find them in your
local bookstore and you can choose what one you like best.

gjcase wrote:

Dave:

Thanks, that does just what I'm after, except I wanted to start at row
2 vs row 1. Changing the range statement fixed that. I appreciate the
help.

BTW, can you recommend a good book for learning excel macro
programming? Right now, I'm just bumbling around tweaking things I've
recorded, buit I'd like to get into it more.

---Glenn

--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=401593

--

Dave Peterson