Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Incrementer en fonction d'une DATE

2 réponses
Avatar
S3com
Bonjour a tous!!!

J'ai un code voir ci joint, il permet de copier de valeur de cellule
donn=E9es,
Sheets(nf).[E51].Copy

je voudrais le modifier ainsi
Sheets(nf).cells(51, 5+ dat) o=F9 dat serait une variable qui
s'incremente de 2 chaque nouvel ann=E9e".

en 2006 dat=3D0, en 2007 dat=3D2, en 2008 dat=3D4 .....

Comment faire pour dat s'incremente en faisant reference =E0 la date?
Merci encore et bonne semaine


For i =3D 0 To Sheets(1).ListeFeuilles.ListCount - 1
If Sheets(1).ListeFeuilles.Selected(i) =3D True Then
nf =3D Sheets(1).ListeFeuilles.List(i)
'nf est le n=B0 de la feuille detenant les informations a copier

'si formules
Sheets(nf).[A4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=3DSelection, _
Address:=3D"", SubAddress:=3D"'" & Sheets(nf).Name & "'!A1", _
TextToDisplay:=3DSheets(nf).Name
'si formules
Sheets(nf).[A8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 2).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[B8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 3).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[D8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 4).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[B4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 5).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[D4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 6).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E13].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 7).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
If Sheets(nf).[E70].Value > 0 Then
Sheets(nf).[E70].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 8).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue

Sheets("recap").[A65000].End(xlUp).Offset(1, 9).Value =3D "=3DRC[-1]"

Else
Sheets(nf).[E71].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 8).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue

Sheets("recap").[A65000].End(xlUp).Offset(1, 9).Value =3D
"=3D(RC[-2]*RC[-1])/100"

End If
'si formules
Sheets(nf).[E51].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 10).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E52].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 12).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E54].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 14).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E55].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 16).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E68].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 18).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue
'si formules
Sheets(nf).[E69].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 20).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=3DTrue

'-----calcul des valeurs et somme
For x =3D 11 To 13 Step 2
Sheets("recap").[A65000].End(xlUp).Offset(1, x).Formula =3D
"=3DRC8*RC[-1]"
Next x
For y =3D 15 To 21 Step 2
Sheets("recap").[A65000].End(xlUp).Offset(1, y).Formula =3D
"=3D(RC8*RC[-1])/100"
Next y
=20
She

2 réponses

Avatar
Phil
Bonjour,

Ta variable dat pourrait être :
dat=(year(date)-2006)*2

Bon courage

"S3com" a écrit dans le message de news:

Bonjour a tous!!!

J'ai un code voir ci joint, il permet de copier de valeur de cellule
données,
Sheets(nf).[E51].Copy

je voudrais le modifier ainsi
Sheets(nf).cells(51, 5+ dat) où dat serait une variable qui
s'incremente de 2 chaque nouvel année".

en 2006 dat=0, en 2007 dat=2, en 2008 dat=4 .....

Comment faire pour dat s'incremente en faisant reference à la date?
Merci encore et bonne semaine


For i = 0 To Sheets(1).ListeFeuilles.ListCount - 1
If Sheets(1).ListeFeuilles.Selected(i) = True Then
nf = Sheets(1).ListeFeuilles.List(i)
'nf est le n° de la feuille detenant les informations a copier

'si formules
Sheets(nf).[A4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:="'" & Sheets(nf).Name & "'!A1", _
TextToDisplay:=Sheets(nf).Name
'si formules
Sheets(nf).[A8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 2).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[B8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 3).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[D8].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 4).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[B4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 5).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[D4].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 6).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E13].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 7).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
If Sheets(nf).[E70].Value > 0 Then
Sheets(nf).[E70].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 8).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True

Sheets("recap").[A65000].End(xlUp).Offset(1, 9).Value = "=RC[-1]"

Else
Sheets(nf).[E71].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 8).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True

Sheets("recap").[A65000].End(xlUp).Offset(1, 9).Value "=(RC[-2]*RC[-1])/100"

End If
'si formules
Sheets(nf).[E51].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 10).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E52].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 12).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E54].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 14).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E55].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 16).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E68].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 18).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True
'si formules
Sheets(nf).[E69].Copy
Sheets("recap").[A65000].End(xlUp).Offset(1, 20).Select
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.PasteSpecial (xlPasteFormats)
ActiveSheet.Paste Link:=True

'-----calcul des valeurs et somme
For x = 11 To 13 Step 2
Sheets("recap").[A65000].End(xlUp).Offset(1, x).Formula "=RC8*RC[-1]"
Next x
For y = 15 To 21 Step 2
Sheets("recap").[A65000].End(xlUp).Offset(1, y).Formula "=(RC8*RC[-1])/100"
Next y

She
Avatar
S3com
Merci ca fonctionne!!

A+