Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function to find a phrase within a range of cells and return the address of the first instance.
I have been trying to write a UDF that takes a cell range and a string
as inputs. It then checks each cell in the supplied range for the string returning the address (i.e. $A$6) of the first instance. I won't embarass myself by putting any of my attempts here. Finding and selecting the cell seems easy to do as a sub-routine but I've struggled mightily to do this as a function. I'd really appreciate any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function to find a phrase within a range of cells and return the
Thanks for the quick reply. I managed to hash the following together
which works admirably: Function FindInRange(Range As Range, What2Find As String) As String ' Finds What2Find in Range and returns address of first instance FindInRange = Range.Find(What:=What2Find, after:=Range(Range.Count)).Address End Function I can't believe it took me so long! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A function to find a phrase within a range of cells and return the
Thanks Tom - that works perfectly too.
I extended this so that it will also return the name of the worksheet that the data was found in. There is probably a neater way of doing this. Public Function FindText(rng As Range, s As String) Dim cell As Range Dim sht As Variant Dim rng2 As Variant FindText = "Not Found" For Each cell In rng If InStr(1, cell, s, xlTextcompare) Then sht = cell.Worksheet.Name rng2 = cell.Address FindText = "'" & sht & "'!" & rng2 Exit For End If Next End Function Tom Ogilvy wrote: public Function FindText(rng as Range, s as String) Dim cell as Range, FindText = "Not Found" for each cell in rng if instr(1,cell,s,xlTextcompare) then FindText = cell.Address exit for end if Next End Function A UDF used in a worksheet can not select a cell. It can only return a value to be displayed in the cell in which it is located - just like any built in function. -- Regards, Tom Ogilvy " wrote: I have been trying to write a UDF that takes a cell range and a string as inputs. It then checks each cell in the supplied range for the string returning the address (i.e. $A$6) of the first instance. I won't embarass myself by putting any of my attempts here. Finding and selecting the cell seems easy to do as a sub-routine but I've struggled mightily to do this as a function. I'd really appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first and last cells in range, return info beside | Excel Worksheet Functions | |||
Find a value in an array and return cell address | Excel Worksheet Functions | |||
Function to return a range of cells from a PivotTable | Excel Worksheet Functions | |||
Find 2nd instance of a word in a range. | Excel Worksheet Functions | |||
Function to return the latest non-zero value in a range of cells . | Excel Worksheet Functions |