Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Range Names using VBA and R1C1
OssieMac:
The first part works great - the selection box is a super idea and works slick. I get stuck as soon as I add the .CurrentRegion to the 2nd "set". When added, the name will not fix to that cell. I presume this code is to "set" a fixed location for the rest of the code to calculate the R1C1 from - Correct? Please look over the following and advise what I'm doing wrong. Sub NameRangeCC() Dim myCell As Object ' Ensure "Pricing" Sheet is Active Worksheets("Pricing").Activate ' Input box to select appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Price List Sub-Total Cell, in Column G", Type:=8) myCell.Select ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="PriceSub", _ RefersToR1C1:=ActiveCell ' The next Cell location is not fixed location in relation to above. ' Therefore, Require the select process, as shown ' Input box to select next appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Options Total Cell, in Column G", Type:=8) myCell.Select ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell SOMEHOW need to "set" the above cell for use by the rest of the code ' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ' Name Current Region based on Active Cell Position ' 1 Row Down, 0 Columns left ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=ActiveCell.Offset(1, 0).CurrentRegion Please advise................. "OssieMac" wrote: Hi BEEJAY, Not sure that I fully understand what you are trying to achieve but here is some sample code that might help. It is not necessarily aligned to what you want to do. When the input box is displayed just click the required cell. Feel free to get back to me. Sub NameRangeCC() Dim myCell As Object 'Ensure correct sheet is activated Worksheets("Sheet1").Activate 'Input box to select a cell Set myCell = Application.InputBox( _ prompt:="Select a cell", Type:=8) myCell.Select 'Names the activecell only ActiveWorkbook.Names.Add Name:="PriceSub", _ RefersToR1C1:=ActiveCell 'Names the current region of the activecell ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell.CurrentRegion 'Input box to select another cell Set myCell = Application.InputBox( _ prompt:="Select a cell", Type:=8) myCell.Select 'Names the current region of the cell that is 3 rows down 'and 1 column to left of the activecell ActiveWorkbook.Names.Add Name:="Disc_Factor", _ RefersToR1C1:=ActiveCell.Offset(3, -1).CurrentRegion -- Regards, OssieMac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Range Names using VBA and R1C1
Hi again BEEJAY,
Since you have name the cell you can continue to use the named cell this way. There are other ways also to save the cell but I won't confuse you with multiple solutions at this point. ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell 'SOMEHOW need to "set" the above cell for use by the rest of the code ' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ' Name Current Region based on Active Cell Position ' 1 Row Down, 0 Columns left ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=Range("OptionSub").Offset(1, 0).CurrentRegion -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Range Names using VBA and R1C1
Greetings OssieMac:
Thanks for your continued efforts. Obviously I don't understand ".CurrentRegion". The following works as I need it to. If you can advise of anything to make it "better" code, I'd appreciate it. Sub NameRangeCC() Dim myCell As Object ' Ensure "Pricing" Sheet is Active Worksheets("Pricing").Activate ' Input box to select appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Price List Sub-Total Cell, in Column G", Type:=8) myCell.Select ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="PriceSub", _ RefersToR1C1:=ActiveCell ' The next Cell location is not fixed location in relation to above. ' Therefore, Require the select process, as shown ' Input box to select next appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Options Total Cell, in Column G", Type:=8) myCell.Select ' The following ARE in "fixed" position, in relation to the cell just named ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell ' The following ARE in "fixed" position, in relation to the cell just named ' Down One Cell, in Same Column ActiveCell.Offset(1, 0).Select ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=ActiveCell ' Down One, Left One ActiveCell.Offset(1, -1).Select ActiveWorkbook.Names.Add Name:="Disc_Factor", _ RefersToR1C1:=ActiveCell ' Right One ActiveCell.Offset(0, 1).Select ActiveWorkbook.Names.Add Name:="CostTotal", _ RefersToR1C1:=ActiveCell etc............................. End Sub Thanks (in advance) again. "OssieMac" wrote: Hi again BEEJAY, Since you have name the cell you can continue to use the named cell this way. There are other ways also to save the cell but I won't confuse you with multiple solutions at this point. ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell 'SOMEHOW need to "set" the above cell for use by the rest of the code ' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ' Name Current Region based on Active Cell Position ' 1 Row Down, 0 Columns left ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=Range("OptionSub").Offset(1, 0).CurrentRegion -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Range Names using VBA and R1C1
Hi BEEJAY,
Have not been able to reply quickly because I am probably on a different time zone to you and also I have been working. (I only do voluntary work these days but never the less working.) Anyway, on "don't understand ".CurrentRegion"." Because you had used the code in your initial post I assumed that you understood it. Your last post suggests that you do not need it for your current project. However, for explanation, CurrentRegion is the range which contains data and is adjacent to the nominated cell/range. It includes all of the range up, down, right and left until it comes to full blank rows and columns or it comes to the top or left of the worksheet. Therefore the CurrentRegion of the ActiveCell includes all that area containing data that surrounds the activecell but remember that in each direction it must reach a full blank row or blank column or the top or left of the worksheet. UsedRange is all that area of the worksheet that has been used. It is measured from the intersection of the first row that contains data and the first column that contains data to the intersection of the last row that contains data and the last column that contains data. -- Regards, OssieMac "BEEJAY" wrote: Greetings OssieMac: Thanks for your continued efforts. Obviously I don't understand ".CurrentRegion". The following works as I need it to. If you can advise of anything to make it "better" code, I'd appreciate it. Sub NameRangeCC() Dim myCell As Object ' Ensure "Pricing" Sheet is Active Worksheets("Pricing").Activate ' Input box to select appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Price List Sub-Total Cell, in Column G", Type:=8) myCell.Select ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="PriceSub", _ RefersToR1C1:=ActiveCell ' The next Cell location is not fixed location in relation to above. ' Therefore, Require the select process, as shown ' Input box to select next appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Options Total Cell, in Column G", Type:=8) myCell.Select ' The following ARE in "fixed" position, in relation to the cell just named ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell ' The following ARE in "fixed" position, in relation to the cell just named ' Down One Cell, in Same Column ActiveCell.Offset(1, 0).Select ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=ActiveCell ' Down One, Left One ActiveCell.Offset(1, -1).Select ActiveWorkbook.Names.Add Name:="Disc_Factor", _ RefersToR1C1:=ActiveCell ' Right One ActiveCell.Offset(0, 1).Select ActiveWorkbook.Names.Add Name:="CostTotal", _ RefersToR1C1:=ActiveCell etc............................. End Sub Thanks (in advance) again. "OssieMac" wrote: Hi again BEEJAY, Since you have name the cell you can continue to use the named cell this way. There are other ways also to save the cell but I won't confuse you with multiple solutions at this point. ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell 'SOMEHOW need to "set" the above cell for use by the rest of the code ' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ' Name Current Region based on Active Cell Position ' 1 Row Down, 0 Columns left ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=Range("OptionSub").Offset(1, 0).CurrentRegion -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assigning Range Names using VBA and R1C1
Hello again Ossiemac:
a little knowledge can be a dangerous thing, eh? Looking back, now I understand why "Not sure that I fully understand what you are trying to achieve". I appreciate you hanging in there with me. It is still thanks to your input that this was resolved. Til next time - have a good one "OssieMac" wrote: Hi BEEJAY, Have not been able to reply quickly because I am probably on a different time zone to you and also I have been working. (I only do voluntary work these days but never the less working.) Anyway, on "don't understand ".CurrentRegion"." Because you had used the code in your initial post I assumed that you understood it. Your last post suggests that you do not need it for your current project. However, for explanation, CurrentRegion is the range which contains data and is adjacent to the nominated cell/range. It includes all of the range up, down, right and left until it comes to full blank rows and columns or it comes to the top or left of the worksheet. Therefore the CurrentRegion of the ActiveCell includes all that area containing data that surrounds the activecell but remember that in each direction it must reach a full blank row or blank column or the top or left of the worksheet. UsedRange is all that area of the worksheet that has been used. It is measured from the intersection of the first row that contains data and the first column that contains data to the intersection of the last row that contains data and the last column that contains data. -- Regards, OssieMac "BEEJAY" wrote: Greetings OssieMac: Thanks for your continued efforts. Obviously I don't understand ".CurrentRegion". The following works as I need it to. If you can advise of anything to make it "better" code, I'd appreciate it. Sub NameRangeCC() Dim myCell As Object ' Ensure "Pricing" Sheet is Active Worksheets("Pricing").Activate ' Input box to select appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Price List Sub-Total Cell, in Column G", Type:=8) myCell.Select ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="PriceSub", _ RefersToR1C1:=ActiveCell ' The next Cell location is not fixed location in relation to above. ' Therefore, Require the select process, as shown ' Input box to select next appropriate cell Set myCell = Application.InputBox( _ Prompt:="Select Options Total Cell, in Column G", Type:=8) myCell.Select ' The following ARE in "fixed" position, in relation to the cell just named ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell ' The following ARE in "fixed" position, in relation to the cell just named ' Down One Cell, in Same Column ActiveCell.Offset(1, 0).Select ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=ActiveCell ' Down One, Left One ActiveCell.Offset(1, -1).Select ActiveWorkbook.Names.Add Name:="Disc_Factor", _ RefersToR1C1:=ActiveCell ' Right One ActiveCell.Offset(0, 1).Select ActiveWorkbook.Names.Add Name:="CostTotal", _ RefersToR1C1:=ActiveCell etc............................. End Sub Thanks (in advance) again. "OssieMac" wrote: Hi again BEEJAY, Since you have name the cell you can continue to use the named cell this way. There are other ways also to save the cell but I won't confuse you with multiple solutions at this point. ' Name Active Cell (only) ActiveWorkbook.Names.Add Name:="OptionSub", _ RefersToR1C1:=ActiveCell 'SOMEHOW need to "set" the above cell for use by the rest of the code ' x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ' Name Current Region based on Active Cell Position ' 1 Row Down, 0 Columns left ActiveWorkbook.Names.Add Name:="Price_Option_Sub", _ RefersToR1C1:=Range("OptionSub").Offset(1, 0).CurrentRegion -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Assigning Range Names in one spreadsheet/worksheet | Excel Discussion (Misc queries) | |||
Assigning set of range names | Excel Programming | |||
Assigning range names to arrays | Excel Programming | |||
Assigning User Names | Excel Programming | |||
Assign names to R1C1 referencing | Excel Programming |