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
| Sub macro1()
Dim LastCol As Integer
Dim LastLig As Long
Dim tableliens As Workbook
Dim x As Variant
With Worksheets(2)
LastLig = .Cells(Rows.Count, 1).End(xlUp).Row
x = Range(Cells(14, 4), Cells(14, LastLig)).Value
Set tableliens = Workbooks.Open("K:\XXXX.xls")
With Application.WorksheetFunction
x = Application.WorksheetFunction.VLookup(Range("M4").Value, tableliens.Range("C2:D142"), 2, False)
tableliens.Close SaveChange = False
End With
End With
With Worksheets(3)
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(81, 2), .Cells(81, LastCol)).FormulaR1C1 = "=SUMIF('Worksheets(2)'!R4C14:R39018C14,'Worksheets(3)'!R1C,'Worksheets(2)'!R4C22:R39018C22)/(R68C+R11C)"
.Range(.Cells(82, 2), .Cells(82, LastCol)).FormulaR1C1 = "=SUMIF('Worksheets(2)'!R4C14:R39018C14,'Worksheets(3)'!R1C,'Worksheets(2)'!R4C23:R39018C23)/(R68C+R11C)"
.Range(.Cells(83, 2), .Cells(83, LastCol)).Value = 100
.Range(.Cells(84, 2), .Cells(84, LastCol)).FormulaR1C1 = "=R82C/R83C"
End With
End Sub |
Partager