Public function to find column letters that contain specific text

[tomleitch]

Hi,

Wondering if anyone can point me in the right direction here....

I'm not sure if it is possible, but what I want to do is make a public function on my workbook that searches for a specific text on a specific row of a specified sheet. (The idea is that then code on other sheets can refer to this)

e.g. I want to search on worksheet "Sheet 1" on row 9 for the text "Hello" and return the column letter that it is found on. For instance calling it v1




Many Thanks
Tom

 

[mumps]

I assume that it will not always be "Sheet 1" on row 9 for the text "Hello". How do you decide which sheet, which row and which text? Where do you want to return the column letter?

 

[tomleitch]

Hi Mumps

The sheet that it needs to look for the text on will remain the same. But multiple sheets in the same workbook may want to refer to the value returned.

The row will remain the same (Row 9).... but the column that it is on may be different and that is the value that I want to get.

To give you some context it is a column header - so I want it to find it if someone adds extra columns in and use it as a reference.


Thanks
Tom

 

[Rick Rothstein]

Will the text you are searching for be the only text in the cell or could it appear among other text within the cell?

If among other text, would you want the word to stand alone and and not be part of a larger word (like searching for "other" and finding it in "brotherhood")?

Is it possible for the text you are searching for to appear in more than one cell on that row?

What exactly do you want returned... the column letter or the cell address?

 

[tomleitch]

Hi Rick,

It will be the only text.

It is a title to a column, so the text won't change.

And it's the column letter I'm after.


Regards,
Tom

 

[mumps]

Try this macro. You can use the variable "colLetter" as a reference.

Sub findColLetter()
    Dim colLetter As String
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet1").Rows(9).Find("Hello", LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        colLetter = Replace(Cells(1, foundVal.Column).Address(False, False), "1", "")
        MsgBox "The column letter is " & colLetter & "."
    Else
        MsgBox ("Text not found.")
    End If
End Sub

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can try...

[table="width: 500"]
[tr]
	[td]Function ColLetter(FindMe As String) As String
  Dim Cell As Range
  Set Cell = Sheets("Sheet1").Rows(9).Find(FindMe, , xlValues, xlWhole, , , False, , False)
  If Not Cell Is Nothing Then ColLetter = Split(Cell.Address, "$")(1)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ColLetter just like it was a built-in Excel function. For example,

=ColLetter(A1)

or...

=ColLetter("Hello")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[tomleitch]

Mumps, Rick, Many thanks for your help. It's very much appreciated.

Cheers
Tom

 

[mumps]

Glad to help.