| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting varying length range
I have 22 different files for which I want to use the same procedure (with
few modifications). The procedure opens a specific file (different for each file containing the macro), inserts columns, inserts formulas, and then fills the formulas down to the last cell in it's column. (Maunally I would simply double-click the autofill handle and Excel would copy down to the last cell based on the column to the left.) When I record the actions, the ending cell is absolute, so I've had to go into each of the 22 modules and manually enter the ending cells for each file. I know there's a way to set the last cell I need as a variable, but even after reading other posts on this subject I can't get it to work. Thanks to all for your suggestions. -- TIA, Nan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting varying length range
Thanks for helping me be a "better poster"! Here's the code. Column H is
empty, so with my autofill, I'm relying on the last non-empty cell in Column G, which is a different cell in each file. Range("H2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]" Selection.AutoFill Destination:=Range("H2:H1882") Range("H2:H1882").Select -- TIA, Nan "Don Guillett" wrote: You should always post your coding efforts for comments and suggestions. To find the last row use the longest column this. Then refer to it instead of the row in the constant lastrow=cells(rows.count,"a").end(xlup).row range("a2:a22") range("a2:a" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... I have 22 different files for which I want to use the same procedure (with few modifications). The procedure opens a specific file (different for each file containing the macro), inserts columns, inserts formulas, and then fills the formulas down to the last cell in it's column. (Maunally I would simply double-click the autofill handle and Excel would copy down to the last cell based on the column to the left.) When I record the actions, the ending cell is absolute, so I've had to go into each of the 22 modules and manually enter the ending cells for each file. I know there's a way to set the last cell I need as a variable, but even after reading other posts on this subject I can't get it to work. Thanks to all for your suggestions. -- TIA, Nan |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting varying length range
So, try adapting my suggestion
lastrow=cells(rows.count,"g").end(xlup).row Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]" Range("h2").AutoFill Destination:=Range("H2:H" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... Thanks for helping me be a "better poster"! Here's the code. Column H is empty, so with my autofill, I'm relying on the last non-empty cell in Column G, which is a different cell in each file. Range("H2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]" Selection.AutoFill Destination:=Range("H2:H1882") Range("H2:H1882").Select -- TIA, Nan "Don Guillett" wrote: You should always post your coding efforts for comments and suggestions. To find the last row use the longest column this. Then refer to it instead of the row in the constant lastrow=cells(rows.count,"a").end(xlup).row range("a2:a22") range("a2:a" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... I have 22 different files for which I want to use the same procedure (with few modifications). The procedure opens a specific file (different for each file containing the macro), inserts columns, inserts formulas, and then fills the formulas down to the last cell in it's column. (Maunally I would simply double-click the autofill handle and Excel would copy down to the last cell based on the column to the left.) When I record the actions, the ending cell is absolute, so I've had to go into each of the 22 modules and manually enter the ending cells for each file. I know there's a way to set the last cell I need as a variable, but even after reading other posts on this subject I can't get it to work. Thanks to all for your suggestions. -- TIA, Nan |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting varying length range
Thank you so much for helping me over this hump. I needed this snipet in
several different places and it works perfectly every time! -- TIA, Nan "Don Guillett" wrote: So, try adapting my suggestion lastrow=cells(rows.count,"g").end(xlup).row Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]" Range("h2").AutoFill Destination:=Range("H2:H" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... Thanks for helping me be a "better poster"! Here's the code. Column H is empty, so with my autofill, I'm relying on the last non-empty cell in Column G, which is a different cell in each file. Range("H2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]" Selection.AutoFill Destination:=Range("H2:H1882") Range("H2:H1882").Select -- TIA, Nan "Don Guillett" wrote: You should always post your coding efforts for comments and suggestions. To find the last row use the longest column this. Then refer to it instead of the row in the constant lastrow=cells(rows.count,"a").end(xlup).row range("a2:a22") range("a2:a" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... I have 22 different files for which I want to use the same procedure (with few modifications). The procedure opens a specific file (different for each file containing the macro), inserts columns, inserts formulas, and then fills the formulas down to the last cell in it's column. (Maunally I would simply double-click the autofill handle and Excel would copy down to the last cell based on the column to the left.) When I record the actions, the ending cell is absolute, so I've had to go into each of the 22 modules and manually enter the ending cells for each file. I know there's a way to set the last cell I need as a variable, but even after reading other posts on this subject I can't get it to work. Thanks to all for your suggestions. -- TIA, Nan |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Selecting varying length range
Glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... Thank you so much for helping me over this hump. I needed this snipet in several different places and it works perfectly every time! -- TIA, Nan "Don Guillett" wrote: So, try adapting my suggestion lastrow=cells(rows.count,"g").end(xlup).row Range("H2").FormulaR1C1 = "=RC[-1]*RC[-7]" Range("h2").AutoFill Destination:=Range("H2:H" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... Thanks for helping me be a "better poster"! Here's the code. Column H is empty, so with my autofill, I'm relying on the last non-empty cell in Column G, which is a different cell in each file. Range("H2").Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-7]" Selection.AutoFill Destination:=Range("H2:H1882") Range("H2:H1882").Select -- TIA, Nan "Don Guillett" wrote: You should always post your coding efforts for comments and suggestions. To find the last row use the longest column this. Then refer to it instead of the row in the constant lastrow=cells(rows.count,"a").end(xlup).row range("a2:a22") range("a2:a" & lastrow) -- Don Guillett Microsoft MVP Excel SalesAid Software "Nan" wrote in message ... I have 22 different files for which I want to use the same procedure (with few modifications). The procedure opens a specific file (different for each file containing the macro), inserts columns, inserts formulas, and then fills the formulas down to the last cell in it's column. (Maunally I would simply double-click the autofill handle and Excel would copy down to the last cell based on the column to the left.) When I record the actions, the ending cell is absolute, so I've had to go into each of the 22 modules and manually enter the ending cells for each file. I know there's a way to set the last cell I need as a variable, but even after reading other posts on this subject I can't get it to work. Thanks to all for your suggestions. -- TIA, Nan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| varying table length in a MACRO | Excel Discussion (Misc queries) | |||
| Web Query from multiple varying length pages | Excel Worksheet Functions | |||
| Varying length records to be sorted | Excel Programming | |||
| using VB to read in a column of values of varying length into an array | Excel Worksheet Functions | |||
| extracting data from a text string of varying length | Excel Discussion (Misc queries) | |||
Linear Mode
