Find a string in a worksheet and return the cell reference

[stever99]

Hi everyone,

I'm a bit of a VBA noob and have been scouring forums trying to piece together the code I need for the following. Hoping someone can help.

I need a function (Excel or VBA) that can check to see if a text string in an adjacent cell exists in a different worksheet in the same workbook. This different worksheet contains many thousands of values, all unique. The formula should return the value from column A of the same row that the string was found in.

I've tried cobbling together match, index and vlookup functions without success. I'm thinking using VBA will be much cleaner.

Thanks in advance for any help you can give me!

 

[Gary McMaster]

Here's one way using the "Find" method.

Gary

Dim oFound As Range
Dim oLookin As Range
Dim sLookFor As String

sLookFor = "xxxyyyzzz" 'Change to suit

Set oLookin = Worksheets("Sheet2").UsedRange 'Change sheet name to suit

Set oFound = oLookin.Find(what:=sLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not oFound Is Nothing Then
    MsgBox oLookin.Range("A" & oFound.Row).Value
End If

 

[stever99]

That does the trick, great!

Would you be able to tell me how I make that macro look in the adjacent cell for the value to find, and then return the value from column A in the active cell instead of displaying the message box?

Again - thanks so much!

 

[Gary McMaster]

I don't know exactly what you mean? The range variable "oFound" contains a reference to the first cell in which "sLookFor" was found, if any. You could use "Offset" to get a reference to adjacent cell(s) as needed. Instead of displaying the message box just put the value in the active cell or wherever else you need it.

Gary

Dim oFound As Range
Dim oLookin As Range

Dim sLookFor As String

sLookFor = "xxxyyyzzz"

Set oLookin = Worksheets("Sheet2").UsedRange

Set oFound = oLookin.Find(what:=sLookFor, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)


Dim oAdjacent As Range

If Not oFound Is Nothing Then
    
    'MsgBox oLookin.Range("A" & oFound.Row).Value
    ActiveCell.Value = oLookin.Range("A" & oFound.Row).Value
    Set oAdjacent = oFound.Offset(0, 1) ' One cell to right
    
End If

 

[stever99]

Perfect - thank you sir!