Bonsoir,
Je suis presque ignorant en VBA...J'ai une macro que je voudrais modifier afin de detecter si une date est présente dans une colonne au lieu du texte "Y"
Ligne à modifier -> .FormulaR1C1 = "=1/(RC5=""Y"")"
Merci !!!!
Sub Update()
Const NomSrc = "PSM_Report_SHU.xlsm"
Dim ClsSrc As Workbook, FSrc As Worksheet, PlgM As Range, DerLig As Long
On Error Resume Next
Set ClsSrc = Workbooks(NomSrc)
If Err Then Err.Clear: Set ClsSrc = Workbooks.Open(NomSrc)
If Err Then MsgBox "Il ne semble pas exister de classeur """ & NomSrc & """" _
& vbLf & "sur """ & CurDir & """.", vbCritical, "Ouverture " & ThisWorkbook.Name
On Error GoTo 0
Set FSrc = ClsSrc.Worksheets(1)
With Intersect(FSrc.[R2:R50000], FSrc.UsedRange)
.FormulaR1C1 = "=1/(RC5=""Y"")"
On Error Resume Next
Set PlgM = .SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Not PlgM Is Nothing Then Intersect(FSrc.[A:R], PlgM.EntireRow).Copy Coms.[A2]
.ClearContents
End With
Dim NbLgn As Long
NbLgn = Coms.UsedRange.Rows.Count - 1
Coms.[S2].Resize(NbLgn).FormulaR1C1 = "=INDEX(Taux,MATCH(RC10,Codes,0))"
Coms.[T2].Resize(NbLgn).FormulaR1C1 = "=RC13*RC[-1]"
End Sub
Je suis presque ignorant en VBA...J'ai une macro que je voudrais modifier afin de detecter si une date est présente dans une colonne au lieu du texte "Y"
Ligne à modifier -> .FormulaR1C1 = "=1/(RC5=""Y"")"
Merci !!!!
Sub Update()
Const NomSrc = "PSM_Report_SHU.xlsm"
Dim ClsSrc As Workbook, FSrc As Worksheet, PlgM As Range, DerLig As Long
On Error Resume Next
Set ClsSrc = Workbooks(NomSrc)
If Err Then Err.Clear: Set ClsSrc = Workbooks.Open(NomSrc)
If Err Then MsgBox "Il ne semble pas exister de classeur """ & NomSrc & """" _
& vbLf & "sur """ & CurDir & """.", vbCritical, "Ouverture " & ThisWorkbook.Name
On Error GoTo 0
Set FSrc = ClsSrc.Worksheets(1)
With Intersect(FSrc.[R2:R50000], FSrc.UsedRange)
.FormulaR1C1 = "=1/(RC5=""Y"")"
On Error Resume Next
Set PlgM = .SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Not PlgM Is Nothing Then Intersect(FSrc.[A:R], PlgM.EntireRow).Copy Coms.[A2]
.ClearContents
End With
Dim NbLgn As Long
NbLgn = Coms.UsedRange.Rows.Count - 1
Coms.[S2].Resize(NbLgn).FormulaR1C1 = "=INDEX(Taux,MATCH(RC10,Codes,0))"
Coms.[T2].Resize(NbLgn).FormulaR1C1 = "=RC13*RC[-1]"
End Sub