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

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

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

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

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