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?
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?
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.
Would you not use the ADDRESS function?
=ADDRESS(ROW(arange)+MATCH(C4,arange,0)-1,COLUMN(arange))
Shorter is better of course though normally, I would just use a UDF.
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
You are not giving us enough information.
test data: enter a,b,c,d,e into A1:A5
will output text to next column when found. If you think a search is possible without a result you will need error handling.Please Login or Register to view this content.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks