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

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

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

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

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.

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