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