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
| Sub RechercheV()
Const PLAGEDEPART As String = "(RC[1],'[ABC.xls]LES DONNEES'!R1C1:R"
Dim derniereLigneMatrice As Long
Dim derniereColonneMatrice As Integer
Dim strRechVFormula As String
Dim oXLApp As New Application
Dim oWB As Workbook
Dim oWKS As Worksheet
Dim oCell As Range
With oXLApp
.Visible = True
.DisplayAlerts = False
Set oWB = .Workbooks.Open("ABC.xls", False, True)
With oWB
Set oWKS = .Sheets("LES DONNEES")
With oWKS
.Select
Set oCell = .Cells(1, 1)
derniereLigneMatrice = oCell.SpecialCells(xlCellTypeLastCell).Row
derniereColonneMatrice = oCell.SpecialCells(xlCellTypeLastCell).Column
End With
.Close False
End With
.Quit
End With
Set oCell = Nothing
Set oWKS =Nothing
Set oWB = Nothing
Set oXLApp = Nothing
strRechVFormula = "=VLOOKUP" & PLAGEDEPART & Trim(Str(derniereLigneMatrice)) & "C" & Trim(Str(derniereColonneMatrice)) & ",2,FALSE)"
ActiveCell.FormulaR1C1 = strRechVFormula
End Sub |
Partager