Salut,
Merci pour ta reponse. J'ai trouver la solution aux problemes ci dessus. En fait je m'y prenais mal pour selectionner les cellules dont j'avais besoin.
Je copie ci dessous tout mon code car j'ai une autre question :
Au travail celui ci fonctionne sans probleme par contre lorsque j'essaie de le faire tourner à mon domicile je recois le message d'erreur suivant :
Erreur 1004
Erreur definie parl'application ou par l'objet
et le dbogage m'amene à la ligne : ActiveCell.FormulaR1C1 = "=RC[-1]/" & TotalVar
Pourtant ce code fonctionne sur mon lieu de travail. Je ne comprend pas.
Merci d'avance pour votre aide
Gregory
Sheets("Loc" ).Select
Range("A1" ).Select
Selection.CurrentRegion.Select
Selection.Clear
Range("A1" ).Select
Sheets("BGLM" ).Range("O15:O217" ).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1" ), Unique:=True
ActiveWindow.SmallScroll Down:=9
Range("A2" ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2" ), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1" ).Select
Do While ActiveCell <> "TRL"
ActiveCell.Offset(1, 0).Select
Loop
Selection.Delete Shift:=xlUp
'***
Sheets("Results" ).Select
Range("A2" ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("PD MTM" ).Select
Range("B2" ).Select
ActiveSheet.Paste
Sheets("Results" ).Select
Range("C2" ).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PD MTM" ).Select
Range("D2" ).Select
ActiveCell.PasteSpecial xlPasteValues
'***
Sheets("Results" ).Select
Range("A3:H3" ).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3:H100" ).Select
Selection.Clear
Range("A1" ).Select
Dim CCYList As Range
Sheets("Loc" ).Select
Set CCYList = Range("A1" ).CurrentRegion
counter = 2
CCY = CCYList.Cells(counter, 1)
Do While CCY <> ""
'start loop here - once per currency
'mtm
Sheets("19700" ).Select
Range("A2:O15" ).Select
Selection.Clear
Range("D2" ).Select
ActiveCell.FormulaR1C1 = "19700"
Range("G2" ).Select
ActiveCell.FormulaR1C1 = CCY
Range("G3" ).Select
Sheets("BGLM" ).Range("I15:U217" ).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:M2" ), CopyToRange:=Range("A10" ), Unique:=False
Range("N11" ).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""D"",RC[-2],-RC[-2])"
Range("N12" ).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""D"",RC[-2],-RC[-2])"
Range("N13" ).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""D"",RC[-2],-RC[-2])"
Range("N14" ).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""D"",RC[-2],-RC[-2])"
Range("N20" ).Select
Selection.FormulaR1C1 = "="
Range("N20" ).Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("N21" ).Select
'realized
Sheets("37300" ).Select
Range("A2:M11" ).Select
Selection.Clear
Range("D2" ).Select
ActiveCell.FormulaR1C1 = "37300"
Range("G2" ).Select
ActiveCell.FormulaR1C1 = CCY
Range("G3" ).Select
Sheets("BGLM" ).Range("I15:U217" ).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:M2" ), CopyToRange:=Range("A6" ), Unique:=False
Range("J18" ).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
Range("K18" ).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
Range("K20" ).Select
ActiveCell.FormulaR1C1 = "=-R[-2]C[-1]+R[-2]C"
Range("K21" ).Select
'summary
Sheets("Results" ).Select
If counter = 2 Then
Range("A3" ).Select
Else
Range("A2" ).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1" ).Select
End If
ActiveCell.FormulaR1C1 = CCY
ActiveCell.Offset(0, 1).Range("A1" ).Select
ActiveCell.Formula = "=IF(ISERROR(VLOOKUP(A3,'PD MTM'!$B$2:$D$200,3,FALSE)),0,VLOOKUP(A3,'PD MTM'!$B$2:$D$200,3,FALSE))"
ActiveCell.Offset(0, 1).Range("A1" ).Select
Sheets("19700" ).Select
Range("N20" ).Copy
Sheets("Results" ).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).Range("A1" ).Select
Sheets("37300" ).Select
Range("K20" ).Copy
Sheets("Results" ).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(0, 1).Range("A1" ).Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
ActiveCell.Offset(0, 1).Range("A1" ).Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-4]"
'end loop here
counter = counter + 1
CCY = CCYList.Cells(counter, 1)
Loop
Sheets("Results" ).Select
Range("B3" ).Select
ActiveCell.Copy
ActiveCell.Offset(1, 0).Range("A1" ).Select
Do While ActiveCell <> ""
ActiveCell.PasteSpecial xlPasteFormulas
ActiveCell.Offset(1, 0).Range("A1" ).Select
Loop
myRows = counter * -1
myRows = myRows + 2
Range("F3" ).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1" ).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" & myRows & "]C:R[-1]C)"
TotalVar = ActiveCell
''***
myRows = myRows * -1
count2 = 0
Range("G3" ).Select
Do While count2 < myRows
ActiveCell.FormulaR1C1 = "=RC[-1]/" & TotalVar
ActiveCell.Offset(1, 0).Range("A1" ).Select
count2 = count2 + 1
Loop
''***
Range("G3" ).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("H3" ).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H3" ).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=ABS(RC[-1])"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H2" ).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H2" ), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom