1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
|
Public Function GetSourceFile() As String
Dim wb As Workbook
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
wb.Sheets.Add
xIndex = 1
For Each link In wb.LinkSources(xlExcelLinks)
GetSourceFile = fso.GetFileName(link)
Next link
End If
End Function
Public Function GetNextCellValueBis(sheetName As String, cellValue As String, row As Integer, col As Integer, idx As Integer) As String
Dim sourceFile As String
Dim foundRange As Range
Dim i As Integer: i = 1
sourceFile = GetSourceFile()
With Workbooks(sourceFile).Sheets(sheetName)
Set foundRange = .UsedRange.Find(What:=cellValue, LookAt:=xlWhole, LookIn:=xlValues)
firstAddr = foundRange.Address
' i = 1
Do While i <= idx
If foundRange Is Nothing Then
Exit Do
End If
i = i + 1
Set foundRange = .UsedRange.FindNext(after:=foundRange)
Loop
End With
If foundRange Is Nothing Then
GetNextCellValueBis = "empty"
Else
GetNextCellValueBis = foundRange.Offset(row, col)
End If
End Function
Sub Test()
MsgBox GetNextCellValueBis("feuille", "Value", 1, 0, 0) 'on affiche la valeur d'en dessous (+1 colonne, +0 ligne) de la cellule "Value"
MsgBox GetNextCellValueBis("feuille", "Value", 1, 0, 1) 'on affiche la valeur d'en dessous de la 2e occurence de la cellule "Value"
MsgBox GetNextCellValueBis("feuille", "Value", 1, 0, 2) 'on affiche la valeur d'en dessous de la 3e occurence de la cellule "Value"
End Sub |
Partager