Search worksheet and get value next to the cell found.

[abenitez77]

I am searching for a string ("SUPPLIER:") in my spreadsheet. If found I want to return the location i.e. B6. Then I want to get the value of the cell next to B6 (the found cell) and put it in a string variable. i.e. var1 = B7. How can I modify my search code below to do that?


Set rSheet = ActiveSheet.UsedRange

'###########################################
currentSheet = ActiveSheet.Index
sheetCount = ActiveWorkbook.Sheets.Count

For counter = 1 To sheetCount

Sheets(counter).Activate

mySearch = "SUPPLIER:"
With Worksheets(counter).UsedRange
Set iSheet2 = .Find(What:=mySearch, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not iSheet2 Is Nothing Then
'found it.
xFilePath = ActiveWorkbook.FullName
xFileName = ActiveWorkbook.Name
foundcell = iSheet2.Address
'ActiveCell.Value
strFound = mySearch
Exit For
End If


End With

Next Counter

 

[JLGWhiz]

Put this after your 'foundCell' statement.

myVal = iSheet2.Offset(0, 1).Value

 

[abenitez77]

thanks!

 

[JLGWhiz]

You're welcome
Regards, JLG