Finding the cell with a specified string

[Don Kline]

I am importing worksheets into a workbook which then need to parse. I then
need to find the cell on a different worksheet which has the string
"value_GuaranteedCashvalue". I don't know what column or row it will end up
in upon import.

If I know the column, I can find the row by using the Match function. What I
can't seem to get is finding the number of the column.

 

[Ron Rosenfeld]

I am importing worksheets into a workbook which then need to parse. I then
need to find the cell on a different worksheet which has the string
"value_GuaranteedCashvalue". I don't know what column or row it will end up
in upon import.

If I know the column, I can find the row by using the Match function. What I
can't seem to get is finding the number of the column.

I'm not sure exactly what you mean by "find the cell".

But here is a simple user defined function (UDF) that will return the address
(including the sheet name) of the first cell that contains that String.

You may want to modify the parameters somewhat. I assumed that you wanted a
*case-sensitive search* where the cell contained *ONLY* the searchString. You
can easily change that in the UDF.

The function will return a #VALUE! error if the search string is not present on
the designated worksheet.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.

Ensure your project is highlighted in the Project Explorer window.

Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=StringAddress(FindString, SheetToSearch )

in some cell. FindString and SheetToSearch can be either literal strings or
cell referencs containing the string.

e.g. =StringAddress(C1, "Sheet2")

==============================================
Option Explicit
Function StringAddress(FindString As String, SheetToSearch As String) As String
Dim ws As Worksheet
Set ws = Worksheets(SheetToSearch)
With ws
StringAddress = ws.Name & "!" & .Cells.Find(What:=FindString, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=True).Address
End With
End Function
====================================



--ron

 

[Don Kline]

Thanks for your help. I am good to go.

BTW - would there be a way to do this using column numbers rather then
letters. Again - it is working for me in its current form.

 

[Ron Rosenfeld]

Thanks for your help. I am good to go.

You're welcome. Glad to help.

BTW - would there be a way to do this using column numbers rather then
letters.

I'm sure there is. However, since I don't know what you mean, I can envision
multiple possibilities. How about you give some specific examples of input to
and output from this function.
--ron

 

[Ron Rosenfeld]

Thanks for your help. I am good to go.

BTW - would there be a way to do this using column numbers rather then
letters. Again - it is working for me in its current form.

Another solution -- this time an **array** formula:

=ADDRESS(MAX((rng=C1)*ROW(rng)),MAX((rng=C1)*COLUMN(rng)))

where

rng refers to the range to search (e.g. Sheet2!A1:Z10000)
*Note that in versions of Excel prior to 2007, the row number cannot
be the very last row on the worksheet.
C1 is the String being searched for.


To enter an *array* formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.
--ron