+ Reply to Thread
Results 1 to 7 of 7

Return Cell Reference

  1. #1
    Registered User
    Join Date
    07-30-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2010
    Posts
    71

    Question Return Cell Reference

    Hi,

    I have a list of values (Reference Nos) in a range on a sheet and I'm looking to find a way of returning the cell reference from the range when a value I'm searching for is found within the range. Can anybody help please?

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Return Cell Reference

    Did you want a formula or a vba solution? For formula, use Match() to get the row and Column() to get column number of a named range. Use Indirect() to get the reference. I added Cell() to show how to get the address.

    =CELL("address",INDIRECT("R"&ROW(aRange)-1+MATCH(B1,aRange,0)&"C"&COLUMN(aRange),FALSE))
    Last edited by Kenneth Hobson; 10-14-2011 at 09:31 AM.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,167

    Re: Return Cell Reference

    Would you not use the ADDRESS function?

    =ADDRESS(ROW(arange)+MATCH(C4,arange,0)-1,COLUMN(arange))
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Return Cell Reference

    Shorter is better of course though normally, I would just use a UDF.

  5. #5
    Registered User
    Join Date
    07-30-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Return Cell Reference

    Many thanks for your help guys. I'm looking for a vba solution so that, when I have the address I can then write back values to cells adjacent to the one i've found. Many thanks

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,167

    Re: Return Cell Reference

    You are not giving us enough information.

    test data: enter a,b,c,d,e into A1:A5

    Please Login or Register  to view this content.
    will output text to next column when found. If you think a search is possible without a result you will need error handling.

  7. #7
    Registered User
    Join Date
    07-30-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2010
    Posts
    71

    Re: Return Cell Reference

    Sorry guys, I'll clarify. I have a value on a form which may or may not exist in a range on a worksheet. Where the value is found in the range I would then like to update cells to the right of it with other values on my form. To do this I am trying to use OFFSET but need the cell reference as a starting point. Here's some pigeon code that may explain it better

    Dim Myrange as Range
    Dim FoundCell as Range
    Dim RecordNo as long

    Set Myrange = Sheets("Sheet1").Range("A1:A20") 'This is where the record number is stored
    RecordNo = MyControl.Value

    Set FoundCell = Myrange.Find(what:=RecordNo)

    If FoundCell Is Nothing Then

    'ADD A NEW RECORD USING THE VALUES FROM THE FORM

    Else

    'UPDATE THE ADJACENT CELLS WITH VALUES FROM THE FORM USING THE OFFSET FUNCTION

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1