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

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

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

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

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

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.

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