pb d'intégration de formule en VBA

[superbog]

Bonjour,

Dans le cadre d'une macro de changement d'année, j'ai besoin d'intégrer la formule suivante en B2

=SI(C2="";"";INDEX('C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!dossier;EQUIV(C2;'C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!nom;0);1))

mais la macro ne fonctionne pas,

Sub chgt_annee_2015_2016()

Dim Wk As Workbook, Sh As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Brigitte\Dropbox\BB\xlbb\affaires2016.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

With ThisWorkbook
  
  For Each Sh In .Worksheets
     If IsNumeric(Sh.Name) Then
     Sh.Delete
     Else: Sh.Activate
        If Range("B1").Value = "dossier" Then
        Range(Cells(2, 1), Cells(5000, 30)).Delete
        Range("B2").Formula = "=IF(C2="";"";INDEX('C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!dossier;EQUIV(C2;'C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!nom;0);1))"
        Selection.AutoFill Destination:=Range("B2:B5000"), Type:=xlFillDefault
        Range("B2:B5000").Select
        End If
     End If
               
Next

End With


MsgBox "changement d'année vers 2016 terminé"

End Sub

Je joins le fichier si vous pouviez m'aider.

Merci d'avance et joyeux noël

 

[BigDaddy154]

Re : pb d'intégration de formule en VBA

Bonsoir,

à tester.

Sub chgt_annee_2015_2016()

Dim Wk As Workbook, Sh As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Brigitte\Dropbox\BB\xlbb\affaires2016.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

With ThisWorkbook
  
  For Each Sh In .Worksheets
     If IsNumeric(Sh.Name) Then
     Sh.Delete
     Else: Sh.Activate
        If Range("B1").Value = "dossier" Then
        Range(Cells(2, 1), Cells(5000, 30)).Delete
        Range("B2").Formula = "=IF(C2="""";"""";INDEX('C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!dossier;EQUIV(C2;'C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!nom;0);1))"
        Selection.AutoFill Destination:=Range("B2:B5000"), Type:=xlFillDefault
        Range("B2:B5000").Select
        End If
     End If
               
Next

End With


MsgBox "changement d'année vers 2016 terminé"

End Sub

Cordialement.

 

[Dranreb]

Re : pb d'intégration de formule en VBA

Bonjour.
Curieux mélange de Formula et de FormulaLocal.
Avec Formula c'est MATCH au lieu de EQUIV et des virgules au lieu de points-virgules.

 

[Philippe Tulliez]

Re : pb d'intégration de formule en VBA

Bonjour,

Curieux mélange de Formula et de FormulaLocal.
Avec Formula c'est MATCH au lieu de EQUIV et des virgules au lieu de points-virgules

Je rajouterais que les méthodes Activate, Select, etc. sont à proscrire car elle ralentisse l'exécution du programme et son parfaitement inutiles en VBA et que la méthode AutoFill n'est pas indispensable et qu'il est plus simple d'écrire la formule directement dans la plage complète..
Petit exemple rapide

 Dim sht As Worksheet
 For Each sht In Worksheets
  If LCase(Left(Trim(sht.Name), 4)) = "trim" Then
   sht.Range("D2:D10000").Formula = "=SUM(A2:C2)"  ' 
  End If
 Next

 

[superbog]

Re : pb d'intégration de formule en VBA

ca ne fonctionne pas, c'est toujours le même problème, la formule

Bonsoir,

à tester.

Sub chgt_annee_2015_2016()

Dim Wk As Workbook, Sh As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Brigitte\Dropbox\BB\xlbb\affaires2016.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

With ThisWorkbook
  
  For Each Sh In .Worksheets
     If IsNumeric(Sh.Name) Then
     Sh.Delete
     Else: Sh.Activate
        If Range("B1").Value = "dossier" Then
        Range(Cells(2, 1), Cells(5000, 30)).Delete
        Range("B2").Formula = [COLOR=#0000cd][B]"=IF(C2="""";"""";INDEX('C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!dossier;EQUIV(C2;'C:\Users\Brigitte\Dropbox\BBcab\Clients.xlsx'!nom;0);1))"[/B][/COLOR]
        Selection.AutoFill Destination:=Range("B2:B5000"), Type:=xlFillDefault
        Range("B2:B5000").Select
        End If
     End If
               
Next

End With


MsgBox "changement d'année vers 2016 terminé"

End Sub

Cordialement.

 

[superbog]

Re : pb d'intégration de formule en VBA

Bonjour Philippe,

Aurais tu la gentillesses de m'indiquer la bonne macro car la je suis un peu perdue

Bonjour,

Je rajouterais que les méthodes Activate, Select, etc. sont à proscrire car elle ralentisse l'exécution du programme et son parfaitement inutiles en VBA et que la méthode AutoFill n'est pas indispensable et qu'il est plus simple d'écrire la formule directement dans la plage complète..
Petit exemple rapide

 Dim sht As Worksheet
 For Each sht In Worksheets
  If LCase(Left(Trim(sht.Name), 4)) = "trim" Then
   sht.Range("D2:D10000").Formula = "=SUM(A2:C2)"  ' 
  End If
 Next

 

[Dranreb]

Re : pb d'intégration de formule en VBA

Appliquer correction expliquée poste #3

Et pour éviter de vous tromper vous pouvez faire :

ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Brigitte\Dropbox\BB\xlbb\affaires2016.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
PréfxClas = ActiveWorkbook.Worksheets(1).[A1].Address(External:=True)
PréfxClas = Left$(PréfxClas, InStr(PréfxClas, "!"))

PréfxClas As String déclaré préalablement, puis plus loin :

 Range("B2:B5000").Formula = "=IF(C2="""","""",INDEX(" & PréfxClas & "dossier,MATCH(C2," & PréfxClas & "nom,0),1))"

ou mieux :

Range("B2:B5000").FormulaR1C1="=IF(RC3="""","""",INDEX(" & PréfxClas & "dossier,MATCH(RC3," & PréfxClass & "nom,0),1))"

ou au pire :

Range("B2:B5000").FormulaLocal = "=SI(C2="""";"""";INDEX(" & PréfxClas & "dossier;EQUIV(C2;" & PréfxClass & "nom;0);1))"