Return Cell Reference

**Mike_e** · 10-14-2011, 08:12 AM

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?

**Kenneth Hobson** · 10-14-2011, 08:58 AM

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))

**Andy Pope** · 10-14-2011, 09:09 AM

Would you not use the ADDRESS function?

=ADDRESS(ROW(arange)+MATCH(C4,arange,0)-1,COLUMN(arange))

**Kenneth Hobson** · 10-14-2011, 09:32 AM

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

**Mike_e** · 10-14-2011, 10:07 AM

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

**Andy Pope** · 10-14-2011, 11:04 AM

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.

**Mike_e** · 10-17-2011, 06:28 AM

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