[VBA-E]Etablir une macro d'une fonction rechercheV

Bonjour,

Tu peux procéder ainsi:
Code:
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
Argy