Redefining a table range with a macro
in [Programming]
|
From: Don Guillett on 22 May 2010 13:55 Something like this sub create5tables() NumRows = 3 NumCols = 6 for i=1 to 5 step 8 cells(i,1).resize(numrows,numcols).name="table"&i next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "OMER" <OMER(a)discussions.microsoft.com> wrote in message news:BDAE54D4-C83B-47EB-99EC-B2C8E444DCE2(a)microsoft.com... > Thank you both for your quick response. > Actually I need to create 5 tables. Each will have the same size as the > others. The starting cell for each of them is fixed. > Let's say the following is given: > NumRows = 'this is Variable > NumCols = 'this is Variable > Starting Cell = ' this is fixed for each table. For example: AA1, BA1, > CA1, etc. > > Resizing them is not working as expected, so I want to delete them and > then > create them again using the new size (determined by NumRows and NumCols). > > All this within a macro. > > Thank youfor your help. > > "Don Guillett" wrote: > >> >> If you don't want to use a self adjusting defined name range as suggested >> tell us how to determine the variables........ >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett(a)gmail.com >> "OMER" <OMER(a)discussions.microsoft.com> wrote in message >> news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com... >> > Hello, >> > I need help creating code to redefine a table within a macro. I used >> > the >> > macro recorder to creata a table with the same name but to point to >> > another >> > locations. >> > >> > ActiveWorkbook.Names("Table1").Delete >> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _ >> > "='Sheet1'!R3C28:R12C37" >> > >> > How do I make this dynamic, so the starting point, #of rows and # of >> > columns >> > are defined by variables? >> > >> > Help is much appreciated. >> > Regards, >> > OMER >> >> . >>
From: OMER on 22 May 2010 20:47 Don, This is how I finally solved it: ShtName = ActiveSheet.Name ActiveWorkbook.Names("Table1").Delete With Sheets(ShtName) Set LastCell = .Cells(ColSize, RowSize) TableRange = .Range(.Range("AB3"), LastCell).Address(ReferenceStyle:=xlR1C1, external:=True) ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" & TableRange End With Regards, OMER "Don Guillett" wrote: > Something like this > sub create5tables() > NumRows = 3 > NumCols = 6 > for i=1 to 5 step 8 > cells(i,1).resize(numrows,numcols).name="table"&i > next i > end sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "OMER" <OMER(a)discussions.microsoft.com> wrote in message > news:BDAE54D4-C83B-47EB-99EC-B2C8E444DCE2(a)microsoft.com... > > Thank you both for your quick response. > > Actually I need to create 5 tables. Each will have the same size as the > > others. The starting cell for each of them is fixed. > > Let's say the following is given: > > NumRows = 'this is Variable > > NumCols = 'this is Variable > > Starting Cell = ' this is fixed for each table. For example: AA1, BA1, > > CA1, etc. > > > > Resizing them is not working as expected, so I want to delete them and > > then > > create them again using the new size (determined by NumRows and NumCols). > > > > All this within a macro. > > > > Thank youfor your help. > > > > "Don Guillett" wrote: > > > >> > >> If you don't want to use a self adjusting defined name range as suggested > >> tell us how to determine the variables........ > >> -- > >> Don Guillett > >> Microsoft MVP Excel > >> SalesAid Software > >> dguillett(a)gmail.com > >> "OMER" <OMER(a)discussions.microsoft.com> wrote in message > >> news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com... > >> > Hello, > >> > I need help creating code to redefine a table within a macro. I used > >> > the > >> > macro recorder to creata a table with the same name but to point to > >> > another > >> > locations. > >> > > >> > ActiveWorkbook.Names("Table1").Delete > >> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _ > >> > "='Sheet1'!R3C28:R12C37" > >> > > >> > How do I make this dynamic, so the starting point, #of rows and # of > >> > columns > >> > are defined by variables? > >> > > >> > Help is much appreciated. > >> > Regards, > >> > OMER > >> > >> . > >> > > . >
From: Don Guillett on 23 May 2010 08:11 If you like it ............... However, what happens with the other 4 tables??? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "OMER" <OMER(a)discussions.microsoft.com> wrote in message news:2BA79513-7E84-4277-9172-56FD1B84035E(a)microsoft.com... > Don, > This is how I finally solved it: > > ShtName = ActiveSheet.Name > ActiveWorkbook.Names("Table1").Delete > With Sheets(ShtName) > Set LastCell = .Cells(ColSize, RowSize) > TableRange = .Range(.Range("AB3"), > LastCell).Address(ReferenceStyle:=xlR1C1, external:=True) > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" & > TableRange > End With > > Regards, > OMER > "Don Guillett" wrote: > >> Something like this >> sub create5tables() >> NumRows = 3 >> NumCols = 6 >> for i=1 to 5 step 8 >> cells(i,1).resize(numrows,numcols).name="table"&i >> next i >> end sub >> >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett(a)gmail.com >> "OMER" <OMER(a)discussions.microsoft.com> wrote in message >> news:BDAE54D4-C83B-47EB-99EC-B2C8E444DCE2(a)microsoft.com... >> > Thank you both for your quick response. >> > Actually I need to create 5 tables. Each will have the same size as the >> > others. The starting cell for each of them is fixed. >> > Let's say the following is given: >> > NumRows = 'this is Variable >> > NumCols = 'this is Variable >> > Starting Cell = ' this is fixed for each table. For example: AA1, >> > BA1, >> > CA1, etc. >> > >> > Resizing them is not working as expected, so I want to delete them and >> > then >> > create them again using the new size (determined by NumRows and >> > NumCols). >> > >> > All this within a macro. >> > >> > Thank youfor your help. >> > >> > "Don Guillett" wrote: >> > >> >> >> >> If you don't want to use a self adjusting defined name range as >> >> suggested >> >> tell us how to determine the variables........ >> >> -- >> >> Don Guillett >> >> Microsoft MVP Excel >> >> SalesAid Software >> >> dguillett(a)gmail.com >> >> "OMER" <OMER(a)discussions.microsoft.com> wrote in message >> >> news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com... >> >> > Hello, >> >> > I need help creating code to redefine a table within a macro. I used >> >> > the >> >> > macro recorder to creata a table with the same name but to point to >> >> > another >> >> > locations. >> >> > >> >> > ActiveWorkbook.Names("Table1").Delete >> >> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _ >> >> > "='Sheet1'!R3C28:R12C37" >> >> > >> >> > How do I make this dynamic, so the starting point, #of rows and # of >> >> > columns >> >> > are defined by variables? >> >> > >> >> > Help is much appreciated. >> >> > Regards, >> >> > OMER >> >> >> >> . >> >> >> >> . >>
From: Don Guillett on 23 May 2010 09:29 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Don Guillett" <dguillett1(a)gmail.com> wrote in message news:%23pEycEn%23KHA.3880(a)TK2MSFTNGP04.phx.gbl... > If you like it ............... However, what happens with the other 4 > tables??? > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "OMER" <OMER(a)discussions.microsoft.com> wrote in message > news:2BA79513-7E84-4277-9172-56FD1B84035E(a)microsoft.com... >> Don, >> This is how I finally solved it: >> >> ShtName = ActiveSheet.Name >> ActiveWorkbook.Names("Table1").Delete >> With Sheets(ShtName) >> Set LastCell = .Cells(ColSize, RowSize) >> TableRange = .Range(.Range("AB3"), >> LastCell).Address(ReferenceStyle:=xlR1C1, external:=True) >> ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:="=" & >> TableRange >> End With >> >> Regards, >> OMER >> "Don Guillett" wrote: >> >>> Something like this >>> sub create5tables() >>> NumRows = 3 >>> NumCols = 6 >>> for i=1 to 5 step 8 >>> cells(i,1).resize(numrows,numcols).name="table"&i >>> next i >>> end sub >>> >>> -- >>> Don Guillett >>> Microsoft MVP Excel >>> SalesAid Software >>> dguillett(a)gmail.com >>> "OMER" <OMER(a)discussions.microsoft.com> wrote in message >>> news:BDAE54D4-C83B-47EB-99EC-B2C8E444DCE2(a)microsoft.com... >>> > Thank you both for your quick response. >>> > Actually I need to create 5 tables. Each will have the same size as >>> > the >>> > others. The starting cell for each of them is fixed. >>> > Let's say the following is given: >>> > NumRows = 'this is Variable >>> > NumCols = 'this is Variable >>> > Starting Cell = ' this is fixed for each table. For example: AA1, >>> > BA1, >>> > CA1, etc. >>> > >>> > Resizing them is not working as expected, so I want to delete them and >>> > then >>> > create them again using the new size (determined by NumRows and >>> > NumCols). >>> > >>> > All this within a macro. >>> > >>> > Thank youfor your help. >>> > >>> > "Don Guillett" wrote: >>> > >>> >> >>> >> If you don't want to use a self adjusting defined name range as >>> >> suggested >>> >> tell us how to determine the variables........ >>> >> -- >>> >> Don Guillett >>> >> Microsoft MVP Excel >>> >> SalesAid Software >>> >> dguillett(a)gmail.com >>> >> "OMER" <OMER(a)discussions.microsoft.com> wrote in message >>> >> news:0349B2F3-7D84-41E0-B1A2-B96488DB971A(a)microsoft.com... >>> >> > Hello, >>> >> > I need help creating code to redefine a table within a macro. I >>> >> > used >>> >> > the >>> >> > macro recorder to creata a table with the same name but to point to >>> >> > another >>> >> > locations. >>> >> > >>> >> > ActiveWorkbook.Names("Table1").Delete >>> >> > ActiveWorkbook.Names.Add Name:="Table1", RefersToR1C1:= _ >>> >> > "='Sheet1'!R3C28:R12C37" >>> >> > >>> >> > How do I make this dynamic, so the starting point, #of rows and # >>> >> > of >>> >> > columns >>> >> > are defined by variables? >>> >> > >>> >> > Help is much appreciated. >>> >> > Regards, >>> >> > OMER >>> >> >>> >> . >>> >> >>> >>> . >>> >
First
|
Prev
|
Pages: 1 2 Prev: Get control of a Userform by the Name Next: get Control of a Butoon placed in the Sheet |