function to find value and return cell reference
rcc
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
Gary''s Student
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
GTVT06
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
T. Valko
Assuming the value occurs only once within the range:
=ADDRESS(MAX((A1:D5=300)*ROW(A1:D5)),MAX((A1:D5=300)*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" <r...@discussions.microsoft.com> wrote in message
news:86079EBB-ED19-4CAC...@microsoft.com...
Steve Miller
>> 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:
>>> Try this array formula**.
>>>
>>> Assuming the value occurs only once within the range:
>>>
>>> =ADDRESS(MAX((A1:D5=300)*ROW(A1:D5)),MAX((A1:D5=300)*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" <r...@discussions.microsoft.com> wrote in message
>>> news:86079EBB-ED19-4CAC...@microsoft.com...
>>>> On Jan 17, 6:52=A0pm, rcc <r...@discussions.microsoft.com> 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
Ron Rosenfeld
>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.
Gord Dibben
Sub try()
Dim cell As Range
Dim i As String
End If
Next cell
End Sub
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
Gord