Relative Reference and AutoFill- Macro
 

I need to copy a formula in a new column for each row. When recording the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the autofill to
adapt. The following is part of the script I think needs adjustment I just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the autofill to
adapt. The following is part of the script I think needs adjustment I just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

Missing a space:

Range("H2").AutoFill Destination:=Range("H2:H" & endrow)

--

Vasant


"Rowan" wrote in message
...
dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording
the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the
autofill to
adapt. The following is part of the script I think needs adjustment I
just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

I also didn't type Cells with a capital C, leave a space between the , and 7
or use a capital U in xlUp etc etc but that is the wonderfull thing about the
VBE: it'll do all of that for me. <g

"Vasant Nanavati" wrote:

Missing a space:

Range("H2").AutoFill Destination:=Range("H2:H" & endrow)

--

Vasant


"Rowan" wrote in message
...
dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording
the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the
autofill to
adapt. The following is part of the script I think needs adjustment I
just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

I deliberately use a capital letter in my variable names and leave out spaces
and when I move to next line if VB doesnt correct proper case and space I
know I have a error.
--
Jim at Eagle


"Rowan" wrote:

I also didn't type Cells with a capital C, leave a space between the , and 7
or use a capital U in xlUp etc etc but that is the wonderfull thing about the
VBE: it'll do all of that for me. <g

"Vasant Nanavati" wrote:

Missing a space:

Range("H2").AutoFill Destination:=Range("H2:H" & endrow)

--

Vasant


"Rowan" wrote in message
...
dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording
the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the
autofill to
adapt. The following is part of the script I think needs adjustment I
just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

Hi Rowan

Your code is really useful. However i have a similar situation but the
starting cell to copy down using autofill might not be H2, might be H3, H5,
H100.

Can I use the actively selected cell to perform the autofill down? let say
it is in the H column?

Thanks you very much!

Leung


"Rowan" wrote:

dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the autofill to
adapt. The following is part of the script I think needs adjustment I just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select

Relative Reference and AutoFill- Macro
 

dim endRow as long
endRow = cells(rows.count,ActiveCell.Column).end(xlup).row
ActiveCell.AutoFill
Destination:=Range(Activecell,Cells(endrow,ActiveC ell.Column))

or
dim EndCell as Range
set EndCell = cells(rows.count,ActiveCell.Column).End(xlup)
ActiveCell.AutoFill Destination:=Range(ActiveCell,EndCell)


--
Regards,
Tom Ogilvy

"Leung" wrote in message
...
Hi Rowan

Your code is really useful. However i have a similar situation but the
starting cell to copy down using autofill might not be H2, might be H3,
H5,
H100.

Can I use the actively selected cell to perform the autofill down? let say
it is in the H column?

Thanks you very much!

Leung


"Rowan" wrote:

dim endRow as long
endRow = cells(rows.count,7).end(xlup).row
Range("H2").AutoFill Destination:=Range("H2:H" &endrow)

should give your the required result.

Regards
Rowan


"Andy Roberts" wrote:

I need to copy a formula in a new column for each row. When recording
the
macro I am using the autofill function however each time I use this
spreadsheet there will be a different number of rows. I need the
autofill to
adapt. The following is part of the script I think needs adjustment I
just
don't know how to adjust it. Thanks in advance for any ideas.

Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H423")
Range("H2:H423").Select