I want to open a workbook from some folder which will be searched by its name in same folder after getting that workbook I want to find some value in it and want to return the row number and column number of that found value. not able to work it out. seek your help guys. below is my code.


Sub SearchFileOpen()

Dim fname As Variant
Dim myPath As String
Dim i As Long
Dim FindString As String
Dim Rng As Range
Dim CapRow As Integer
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Wbbk As Workbook

Set Wbbk = ThisWorkbook


myPath = "F:\BOM2\"
fname = Dir(myPath & Sheet1.Range("A2").Value & "*.xl*")

'Set Wbk = Workbooks.Open(myPath & fname, False, True)

If fname <> "" Then
Workbooks.Open (myPath & fname)
End If

Set Wb = ActiveWorkbook
Set Ws = ActiveSheet

For i = 2 To 14
FindString = Sheet1.Cells(1, i)
With ActiveWorkbook.ActiveSheet.Range("C:C")
If Trim(FindString) <> "" Then
With .Range("C:C")
Set Rng = .Find(What:=FindString, _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
CapRow = Rng.Row
MsgBox Rng.Row
End If
End With
End If
End With
Next

End Sub