Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Return Single Cell Address From Range

  1. #1
    Join Date
    4th May 2007
    Posts
    134

    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)

    VB:
     ' 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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Convert Ranges To Column Headers

    Which Find_Range function would this be?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,039

    Re: Convert Ranges To Column Headers

    OK, duplicate post has been deleted.
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



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

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

  4. #4
    Join Date
    4th May 2007
    Posts
    134

    Re: Convert Ranges To Column Headers

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,039

    Re: Convert Ranges To Column Headers

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

    Wigi

    Excel MVP 2011, 2012, 2013



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

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

  6. #6
    Join Date
    4th May 2007
    Posts
    134

    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

    VB:
    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:
    VB:
    J5:J10, K10:K20, X70:Z75 
    
    
    Instead of the range list, I want the header of each column in the list
    So I want
    VB:
    J1, K1, X1, Y1, Z1 
    
    
    The list of ranges is typically very large.

    Thank you very kindly for your help
    -DWildman

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    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;
    VB:
    Sheets("ID List"). Range("D" & I) = Found.Cells(1,1).Address(False, False) 
    
    

  8. #8
    Join Date
    4th May 2007
    Posts
    134

    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
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,314

    Re: Return Single Cell Address From Range

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

    Cheers
    Andy


  10. #10
    Join Date
    4th May 2007
    Posts
    134

    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
    VB:
    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.
    VB:
    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 at 02:29.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Return Cell Address Of Value In Range
    By gillrajb in forum EXCEL HELP
    Replies: 1
    Last Post: August 23rd, 2008, 05:00
  2. Return Nth Value From Single Column Range
    By Michael Avidan in forum EXCEL HELP
    Replies: 17
    Last Post: April 30th, 2008, 10:45
  3. Return Value & Address Of Named Range
    By DisDe in forum EXCEL HELP
    Replies: 2
    Last Post: February 20th, 2008, 22:13
  4. Address Single Cells In Named Range
    By tsrwebman in forum EXCEL HELP
    Replies: 7
    Last Post: December 22nd, 2006, 21:12
  5. VBA: using Offset to return a range, not a single cell
    By Vonbloodbath in forum EXCEL HELP
    Replies: 2
    Last Post: June 21st, 2005, 00:50

Bookmarks

Posting Permissions

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