Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Named Ranges (2002)

    I can name a range of cells in Excel.

    Is there a way to then incorporate a single cell from that named range into a formula?

    A named cell is a great thing in a formula, because it can make the formula match up better with an original hard copy source, and because it is always absolutely addressed. But sometimes there are sets of cells for which a name makes sense, but for which relative addressing would still be useful.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Named Ranges (2002)

    Any single contiguous named range can be addressed by cell. (Not sure about multiple noncontigous areas in a single range, haven't tried it). For starters you can use =OFFSET(reference,rows,cols,height,width). For example

    =OFFSET(mynamedrange,0,0,1,1) returns the cell in the first row, leftmost column of the range named "mynamedrange"
    =OFFSET(mynamedrange,2,1,1,1) returns the cell which is third row, second column of the range named "mynamedrange"

    You can also get single cells out of named ranges with all the table lookup type functions, such as =INDEX(array,row_num,column_num) and so forth.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    You can use relative addressing in a name.

    For example, let us say you are on cell C2 and open the define name box (Insert, name, define).

    Name: Test
    Refersto: =B1

    Note I used NO dollar signs.

    This name will now refer to a cell one column to the left and one row up, so if you enter

    =Test

    into cell Z10, Test in that cell refers to cell Y9.

    So the offset you get depends on two things:

    1. the cell that is active when you define the name
    2. De exact address(es) you use in the name, relative to the active cell

    I leave it up to the readers to figure out what this implies:

    Active cell: A5
    Name: Test2
    Refersto: =SUM(A$1:A$4)

    Prize question:
    What can be done with such a name???
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Hi John, I need to do something similar and I'm having difficulty working out how to do so, too.

    I have a userform with 10 textboxes in it (named textbox1 to textbox10, for want of anything better) and I need to pick up the contents of a 1-column, 10-row named range and put them in the textboxes. I can't specify a particular place to start because *this is only one of half a dozen ranges that I'm going to have to refer to according to where the user was when he/she set the macro running, so I need to name a variable according to which range is to be used and use that.

    I did try a loop that ran through the 10 cells and deposited the contents in the textboxes - or it would have (hopefully) if I'd been able to find a way to refer to the textboxes dynamically! eg frmSectionChange.Textbox + i.value = ... doesn't work, and naming the textbox name as a variable eg bxName = "textbox" + l then frmSectionChange.bxName.value just returns that bxName is not defined. I got this far:

    Dim c, i, l, bxName As String
    For Each c In Range(rngName)
    i = ThisWorkbook.secName
    l = 1
    bxName = "Textbox" + l
    With c
    If Left(.Value, 1) <> "#" Then frmSectionChange.TextBox1.Value = .Value Else frmSectionChange.bxName.Value = ""
    i = i + 1
    l = l + 1
    End With
    Next c

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Now I've got a headache and I can't think what I need to do!

    <img src=/S/please.gif border=0 alt=please width=31 height=23> <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Ranges (2002)

    >> Now I've got a headache and I can't think what I need to do!

    Take an aspirin. If only all questions in the Lounge were that simple!

    .

    .

    .

    .

    You can refer to the series of text boxes this way:

    frmSectionChange.Controls("TextBox" & i)
    or
    frmSectionChange.Controls(bxName)

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Thank you, Hans, the *second* half of your reply as good as supplied the *first* half!

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Beryl M


  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Er ... I've just realised ... although your answer solved my problem of how to refer to a userform textbox, I still can't work out how to refer to a cell within a named range? I know you can refer to the top left cell within a range as A1 of that range, but I can't work out how to word it!

    Can I ask another question while I'm here? <img src=/S/smile.gif border=0 alt=smile width=15 height=15> In that loop I quoted in my earlier post, the sub is snagging on the line "For each c in range(rngIncome)", saying there's an application defined error? I tried adding activesheet to the front of range, and adding a line to specify a variable as the range and then saying for each c in rngA, but nothing will get it to run! What am I doing wrong, please? For info, the sub is in the initialisation of a form (I'm trying to put the contents of the 10 cells as the value in 10 textboxes).

    Any help would be greatly appreciated!
    Beryl M


  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Ranges (2002)

    Hi Beryl,

    You can use the Cells array to refer to a cell within a (named) range: the cell in the 2nd row and 4th column of a range named "Woody" is
    ActiveSheet.Range("Woody").Cells(2, 4)

    What is rngIncome? The prefix "rng" points to a variable of type Range. If that is the case, you would use
    For Each c In rngIncome

    If rngIncome is a string variable holding the name of a named range, Activesheet.Range(rngIncome) should work. Another way to refer to a named range is
    ActiveWorkbook.Names("Woody").RefersToRange

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Perfick!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Something I'm confused about though - I always understood that the c in For Each c in Range... referred to each cell in that range, but it seems to be picking up the content of the cell instead - which is fine, I've just used it straight without having to add .value to it, but how would you refer to each cell within the range? There doesn't seem to be a variable type of cell?

    No urgency - just curiosity! (although I've no doubt I'll find a use for it soon!)

    Many thanks!
    Beryl M


  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    If you have a range named "Woody" then you can do something like this:

    <pre>Dim oCell As Range
    For Each oCell In Range("Woody")
    MsgBox oCell.Address & " = " & oCell.Value
    Next oCell
    </pre>

    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Ranges (2002)

    There is no separate Cell object; a cell is a tiny Range. Let's look at the following piece of code

    Dim c As Range
    For Each c In Range("B12:E37")
    ...
    Next c

    The c in this bit of code is a Range object consisting of one cell. You can get at all properties and methods that belong to the Range object, for example Address, Borders, Formula, Interior and of course, also Value. Value is the default property, so in many cases you can use plain c to refer to its value, but c is still a Range object.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Hi Hans, hope you had a nice weekend! I'm confused again (which seems to be pretty much a constant state of affairs these days, sorry!).

    I have several ranges which I am referring to via a variable which is told separately which rang to look at this time, and although all the ranges are 1 cell wide, they are anything from 2 to 12 cells high. I have been using the code you gave to refer to each cell within each range ( eg "ActiveSheet.Range("Woody").Cells(2, 4)") but I've now come across something that leaves me stuck. Again.

    I need to put the contents of each cell in a named variable, Std1 thru up to 12, so at the moment I have twelve lines that look like this:

    Std1 = ActiveSheet.Range(rngWhichStd).Cells(1, 1)
    If ActiveSheet.Range(rngWhichStd).Cells(2, 1).Value <> "" Then Std2 = ActiveSheet.Range(rngWhichStd).Cells(2, 1)
    etc

    However, when the range runs out of cells, the code doesn't stop - it's as if it's just using the range as a starting point and counting up to 12 cells down, even if the range is a lot smaller. Net result is the latter part of the 12 ends up containing the content of the cells below the range, which I don't want.

    I know this could be done by a For Each loop, which would hopefully stop in the right place, but how would I then refer to the named variables, Std1 thru Std12? I presume it would something like "Std" + i where i is the current number, but I can't work out how it would go together ...

    Many thanks in advance!
    Beryl M


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Ranges (2002)

    You can use Controls("Text" & i) to refer to controls named Text1, Text2 etc. But you can't do the same for variables. Instead, you would use an array, say

    Dim Std(1 To 12)

    You can then refer to Std(i) where i is a numeric variable. If you want the array to be dynamic, i.e. determine its size in runtime, you could use code like this:

    Dim Std() As Variant
    Dim rng As Range
    Dim i As Integer
    ' Restrict to used range
    Set rng = Range(Range(rngWhichStd).Cells(1, 1), Range(rngWhichStd).Cells(12, 1).End(xlUp))
    ' Redimension array
    ReDim Std(1 To rng.Cells.Count)
    ' Loop through cells
    For i = LBound(Std) To UBound(Std)
    ' Store cell value in array element
    Std(i) = rng.Cells(i).Value
    Next i

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Named Ranges (2002)

    Thanks, Hans, and that answers my question beautifully ... except I keep coming up with new ones!

    In this case, first, the Std + i variables have to be public, in order to carry from one module to another, but when I put it at the top as a Public statement the de###### informed me I wasn't allowed to!

    And second, for some reason the array is picking up the last value of a cell *before* the array and the first *in* it - ie if rngWhichStd contains B19-B26, the array picks up B18's value for Std1 and B19's value for Std2 then stops!

    <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15> ?!??! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Ranges (2002)

    1. You can only declare a variable as Public in a standard module, not in the module behind a userform. So create a standard module and declare the array there:

    Public Std() As Variant

    2. Are you sure about the definition of rngWhichStd? In my test spreadsheet, Std contains the correct values.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •