| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative reference? in macro | Excel Worksheet Functions | |||
| Using relative reference in macro | Excel Discussion (Misc queries) | |||
| Relative reference autofill increment other than +1 | Excel Discussion (Misc queries) | |||
| Relative reference in macro | Excel Programming | |||
| Relative Reference in a Macro | Excel Programming | |||
Linear Mode
