Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Return Single Cell Address From Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Return Single Cell Address From Range

    Hello again
    I figure while Im on here I may as well pick your brains for another dilemma Im having.
    Thanks in advance for your thoughts.

    I have a list of ID's on one sheet that are also located in a large matrix on another sheet.
    This macro uses the Find_Range function to find each ID within the matrix and return the column header where the ID was first located.
    There is also a line which return a list of ranges, indicating all the places where the ID was found.

    What I want is to convert the list of ranges into a list of corresponding column headers (ie row 1 of all columns in the range)

    Code:
        ' Number of id's in list
        RowCnt = Application.WorksheetFunction.CountA(Columns("A"))
       
        For I = 2 To RowCnt
    
            LookFor = Sheets("ID List").Range("A" & I)
            Set InRng = Sheets("Matrix").Cells
            Set Found = Find_Range(LookFor, InRng, xlValues, xlWhole)
            On Error Resume Next    ' If value is not found
            Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _
                .Cells(1, Range(Found.Address).Column) ' Return column header
            Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses
            On Error GoTo 0
    
       Next I
    Thanks again

    -DWildman

  • #2
    Re: Convert Ranges To Column Headers

    Which Find_Range function would this be?
    Boo!

    Comment


    • #3
      Re: Convert Ranges To Column Headers

      OK, duplicate post has been deleted.
      Regards,

      Wigi

      Excel MVP 2011-2014

      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

      Comment


      • #4
        Re: Convert Ranges To Column Headers

        This Find_Range function http://www.ozgrid.com/forum/showthre...98788#poststop

        Comment


        • #5
          Re: Convert Ranges To Column Headers

          Did you copy Aaron's code as well?
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: Convert Ranges To Column Headers

            Hi Wigi - Yes the Find_Range function is from Aaron Blood as per the link I posted.
            Let me ask this differently...
            If we look at this line

            Code:
            Sheets("ID List").Range("D" & I) = Found.Address(False, False) ' Return cell address or range of addresses
            "Found" is a range of cells which may be disjoint. This line returns a list of ranges.
            The output looks like this:
            Code:
            J5:J10, K10:K20, X70:Z75
            Instead of the range list, I want the header of each column in the list
            So I want
            Code:
            J1, K1, X1, Y1, Z1
            The list of ranges is typically very large.

            Thank you very kindly for your help
            -DWildman

            Comment


            • #7
              Re: Convert Ranges To Column Headers

              I can't see how you derived at J1, K1, X1, Y1, Z1 from the ranges J5:J10, K10:K20, X70:Z75??? However, you can always make any range relative to itself, or another range. See details of Relative to: in the VBA help on the Address Property.

              Or, use code along the lines of;
              Code:
              Sheets("ID List"). Range("D" & I) = Found.Cells(1,1).Address(False, False)

              Comment


              • #8
                Re: Return Single Cell Address From Range

                Hi Dave. Thanks for the reply.
                I want the column headers (row 1 for every column in the range). Range J5:J10 contains only column J so I want the header row which is J1. Range X70:Z75 contains columns X, Y and Z so I want X1, Y1, Z1.

                The line you posted returns the address of the first occurrence. I need to convert this into the header row which I have done with this line
                Code:
                Sheets("RateID Count").Range("C" & I) = Sheets("Matrix") _
                            .Cells(1, Range(Found.Address).Column) ' Return column header
                But this again only returns the column headr of the first occurrence. I need the column header for every occurence.

                Thanks
                -DWildman

                Comment


                • #9
                  Re: Return Single Cell Address From Range

                  Code:
                  Msgbox Range("X70:Z75").entirecolumn.Rows(1).address

                  Cheers
                  Andy

                  Comment


                  • #10
                    Re: Return Single Cell Address From Range

                    Hi Andy - Thank you but this is not quite what I need. The range could be something like
                    Code:
                    Range("X70:Z75", "AA9")
                    so your line gives me X1:AA1 but I only want X1, Y1, Z1, and AA1. I also need the value of the cell, not the address.

                    I came up with this which works fine but does not seem very elegant.
                    Code:
                    For Each cl In Found
                                
                                Sheets("ID List").Cells(I, "G").FormulaR1C1 = _
                                Sheets("ID List").Cells(I, "G").FormulaR1C1 & ", " & Sheets("Active Matrix").Cells(1, cl.Column)
                               
                            Next cl
                    I am just appending each value to a list in one cell in "ID List". If anyone has a nicer way of doing this I am certainly all ears.

                    Thanks
                    -DWildman
                    Last edited by DWildman; May 19th, 2007, 02:29.

                    Comment


                    • #11
                      Re: Return Single Cell Address From Range

                      Not really sure what you are trying to do.

                      But nocontiguous ranges will required a loop and the use of Area
                      Code:
                          Set Found = Range("J5:J10, K10:K20, X70:Z75")
                          For Each rngArea In Found.Areas
                              Debug.Print rngArea.EntireColumn.Rows(1).Address
                          Next
                      Again, I have just outputted to range addresses. You will need to adapt for your purpose.

                      Cheers
                      Andy

                      Comment


                      • #12
                        Re: Return Single Cell Address From Range

                        That will work just fine. Thank you very much and sorry if I wasnt being clear with my objective.
                        _DWildman

                        Comment

                        Trending

                        Collapse

                        There are no results that meet this criteria.

                        Working...
                        X