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
|
Sub Formule()
Dim nCA As Integer
Dim nLD As Integer
Dim nCD As Integer
Dim Col As String
'recherche de la dernière ligne et colonne utilisée
'(on ne sait pas si la bonne feuille est celle qui est active ???)
With ActiveSheet 'un 'Worksheets("Nom de la Feuille")' serait mieux
nCD = .Cells.Find("*", .[A1], xlFormulas, , xlByColumns, xlPrevious).Column
nLD = .Cells.Find("*", .[A1], xlFormulas, , xlByRows, xlPrevious).Row
End With
'récupère la ou les lettres de la dernière colonne utilisée
Col = Left(Columns(nCD).Address(0, 0), InStr(Columns(nCD).Address(0, 0), ":") - 1)
'inscription du Nom
ActiveWorkbook.Names.Add Name:="desc", RefersTo:="=description!A1:" & Col & nLD
ActiveWorkbook.Names("desc").Comment = ""
Workbooks("articles.xlsx").Activate
'ici non plus ???
With ActiveSheet
nCA = .Cells.Find("*", .[A1], xlFormulas, , xlByColumns, xlPrevious).Column
End With
Col = Left(Columns(nCA + 1).Address(0, 0), InStr(Columns(nCA + 1).Address(0, 0), ":") - 1)
Cells(1, nCA + 1) = "description"
With Cells(2, nCA + 1)
.Formula = "=VLOOKUP(" & Col & "2,description.xlsx!desc,3,FALSE)"
.AutoFill Destination:=Range("AZ2:AZ545"), Type:=xlFillDefault
End With
Range("AZ2:AZ545").Select
End Sub |
Partager