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
> > >
> > >
|
Similar Threads
