finding the address of cell that contains a string

[christhedonstar]

what formulae could be used to find the addreess of a cell in a worksheet that contains a specific string?

Thanks,

Chris

 

[TonyJollans]

In theory it might be possible, but in practice a formula cannot randomly search a worksheet, and you should write your own function for this, unless you can refine the criteria a little.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Loomah]

i'm even more sceptical that this could be done with a formula - unless you are looking to 'flag' cells in a range that contain a certain string. if that is the case then have a look at the find and search worksheet functions.

if it's a case of finding a cell anywhere in a worksheet (or smaller range) that contains a string then i think it's a case of using vba
eg, as a start
mycelladdress = cells.find("my string to find").address

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

WHY do you need to do this?

Please explain the PURPOSE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

If you're looking for a single instance of a text in a 1-dimensional array (e.g. a column) you can of course use MATCH (and it works with wild-cards if you need to search for a substring). But I'd guess you have a bigger search or you wouldn't be posting...

 

[christhedonstar]

I ended up using code for it as follows (I hacked it together quickly so it has issues as per the comments):

Option Explicit

'Careful using this function it will reference the sheet in the workbook it first finds the sheetname in
'It will also just find the first matching cell that contains the string you're searching for
Public Function GetRowOrColNumberContainingString(ByVal searchString As String, ByVal sheetName As String, Optional optionStr As String = "ROW") As Variant

Dim ws As Worksheet
Dim wb As Workbook

For Each wb In Workbooks
On Error Resume Next
Set ws = wb.Worksheets(sheetName)
Next wb

On Error GoTo ErrHandler

If ws Is Nothing Then
GetRowOrColNumberContainingString = "Could not find " & sheetName & " in any of the workbooks in your excel session"
GoTo exitLabel
End If

Dim resultCell As Range
Set resultCell = ws.Cells.Find(What:=searchString, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

If UCase(optionStr) = "ROW" Then
GetRowOrColNumberContainingString = resultCell.Row
ElseIf UCase(optionStr) = "COL" Then
GetRowOrColNumberContainingString = resultCell.Column
Else
GetRowOrColNumberContainingString = "You need to pass the word ROW or COL as the 2nd argument for this function to work"
End If

exitLabel:
Exit Function

ErrHandler:
GetRowOrColNumberContainingString = Err.Description
GoTo exitLabel

End Function