Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
Hi,
I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
Function findit(v As Variant, r As Range) As String
findit = "" For Each rr In r If rr.Value = v Then findit = rr.Address Exit Function End If Next End Function will find either numbers or text. Use like: =findit("happy",A1:C100) -- Gary''s Student - gsnu200765 "rcc" wrote: Hi, I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
On Jan 17, 6:52*pm, rcc wrote:
Hi, I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. *For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc you can create a macro similar to something along the lines of this: Sub try() Dim cell As Range Dim i As String i = InputBox("Search for:") For Each cell In Range("A1:D5") If cell.Value = i Then MsgBox (i & " Is in cell " & cell.Address) End If Next cell End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
Try this array formula**.
Assuming the value occurs only once within the range: =ADDRESS(MAX((A1:D5=300)*ROW(A1:D5)),MAX((A1:D5=30 0)*COLUMN(A1:D5)),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "rcc" wrote in message ... Hi, I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
Did you find a macro that worked??? I know it's been four years, but I want to re-new this "Conversation." I tried the three suggestions and keep getting "error" so there must be some macro-writing concepts I've forgotten. (I used to be a wizard at Excel--now retired too long.) .. .. Please help.
On Thursday, January 17, 2008 7:52 PM rc wrote: Hi, I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc On Thursday, January 17, 2008 8:27 PM GarysStuden wrote: Function findit(v As Variant, r As Range) As String findit = "" For Each rr In r If rr.Value = v Then findit = rr.Address Exit Function End If Next End Function will find either numbers or text. Use like: =findit("happy",A1:C100) -- Gary''s Student - gsnu200765 "rcc" wrote: On Thursday, January 17, 2008 9:50 PM T. Valko wrote: Try this array formula**. Assuming the value occurs only once within the range: =ADDRESS(MAX((A1:D5=300)*ROW(A1:D5)),MAX((A1:D5=30 0)*COLUMN(A1:D5)),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "rcc" wrote in message ... On Saturday, January 19, 2008 2:48 PM GTVT06 wrote: On Jan 17, 6:52=A0pm, rcc wrote: et e a he you can create a macro similar to something along the lines of this: Sub try() Dim cell As Range Dim i As String i =3D InputBox("Search for:") For Each cell In Range("A1:D5") If cell.Value =3D i Then MsgBox (i & " Is in cell " & cell.Address) End If Next cell End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
On Tue, 26 Jun 2012 23:21:04 GMT, Steve Miller wrote:
Did you find a macro that worked??? I know it's been four years, but I want to re-new this "Conversation." I tried the three suggestions and keep getting "error" so there must be some macro-writing concepts I've forgotten. (I used to be a wizard at Excel--now retired too long.) .. .. Please help. Since this is such an old thread, I don't know what it is exactly you are looking for, or even to whom you are asking the question. Perhaps if you state your requirements clearly, I can provide you with something that will be useful. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
function to find value and return cell reference
This one revised works...............removed the "3D" part
Sub try() Dim cell As Range Dim i As String i = InputBox("Search for:") For Each cell In Range("A1:D5") If cell.Value = i Then MsgBox (i & " Is in cell " & cell.Address) End If Next cell End Sub This one works.................... =findit("happy",A1:D5) Function findit(v As Variant, r As Range) As String findit = "" For Each rr In r If rr.Value = v Then findit = rr.Address Exit Function End If Next End Function Both of these would be placed into a general module. Gord On Tue, 26 Jun 2012 23:21:04 GMT, Steve Miller wrote: Did you find a macro that worked??? I know it's been four years, but I want to re-new this "Conversation." I tried the three suggestions and keep getting "error" so there must be some macro-writing concepts I've forgotten. (I used to be a wizard at Excel--now retired too long.) .. .. Please help. On Thursday, January 17, 2008 7:52 PM rc wrote: Hi, I'm looking for a function that will find a value somewhere on a spreadsheet and return its cell reference. For instance, I'd like to be able to have a function that will take '300' and the data range 'A1:D5' and then return the cell that '300' is found in, i.e. D3 (if indeed D3 contains '300'). Do I need to write a macro for this? Thanks, rcc On Thursday, January 17, 2008 8:27 PM GarysStuden wrote: Function findit(v As Variant, r As Range) As String findit = "" For Each rr In r If rr.Value = v Then findit = rr.Address Exit Function End If Next End Function will find either numbers or text. Use like: =findit("happy",A1:C100) -- Gary''s Student - gsnu200765 "rcc" wrote: On Thursday, January 17, 2008 9:50 PM T. Valko wrote: Try this array formula**. Assuming the value occurs only once within the range: =ADDRESS(MAX((A1:D5=300)*ROW(A1:D5)),MAX((A1:D5=30 0)*COLUMN(A1:D5)),4) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "rcc" wrote in message ... On Saturday, January 19, 2008 2:48 PM GTVT06 wrote: On Jan 17, 6:52=A0pm, rcc wrote: et e a he you can create a macro similar to something along the lines of this: Sub try() Dim cell As Range Dim i As String i =3D InputBox("Search for:") For Each cell In Range("A1:D5") If cell.Value =3D i Then MsgBox (i & " Is in cell " & cell.Address) End If Next cell End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Find a value in a table and return the cell or column reference | Excel Discussion (Misc queries) | |||
Find a value in a table and return the cell reference | Excel Worksheet Functions | |||
Find the Cell Reference Resulting from MAX function | Excel Worksheet Functions | |||
return the column reference number of a function result | Excel Discussion (Misc queries) |