VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Zish · 25 Juin 2013

Bonjour,

Je me prends la tête sur un bout de mon code que j'essaye d'optimiser, qui utilise la formule SOMMEPROD pour faire une sorte de recherche complexe (je parcours un tableau composé d'une première colonne avec un code produit et d'une seconde colonne avec le nom d'un client, je veux récupérer un nombre de jour de DLC qui est renseigné dans un autre tableau).

Elle marche bien lorsqu'il s'agit de la formule transcrite par l'enregistreur de macro :

Code:

 ActiveCell.Offset(0, 1).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

Mais je travaille sur une version amélioré qui passe par des variables, notamment parce que cette même formule est répétée sur 9 colonnes.
Sans tenir compte du test If et du rajout +1, j'essaye au moins d'obtenir que le Sommeprod fonctionne, j'en suis arrivé à ca :

Code:

Dim tablo1 As String
Dim tablo2 As String
Dim tablo3 As String

'Dim code As String
'Dim client As String

tablo1 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$A$1:$A$10000"
tablo2 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$D$1:$D$10000"
tablo3 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$F$1:$F$10000"

Range("C2").Select
'On Error Resume Next
For i = 3 To fin
    code = Cells(i, 1).Value
    For j = 4 To 12
        client = Cells(1, j).Value
        Cells(i, j).Formula = Application.Evaluate("=SumProduct((" & tablo1 & "=" & code & ") * (" & tablo2 & "=" & client & ") * (" & tablo3 & ")))")
    Next j
Next i

'On Error GoTo 0

La formule me ressort systématiquement des #VALEUR! donc je me demande d'où vient mon problème, est ce que mes variables qui contiennent les chemins des tableaux sont bien écrites ? Est ce que cela vient de la délcaration des variables code et client ? Est ce que le fait de faire une recherche à la fois de valeurs numérique et alphanumérique pose problème ?
J'ai epluché pleins de sites français et anglais traitant des formules SUMPRODUCT et Evaluate, mais pas moyen d'arriver à quelque chose qui fonctionne !
Ah et lorsque je n'utilise pas le Evaluate mais directement le Sumproduct, la macro plante en me mettant le message Incompatibilité de type...d'où mon intuition que le problème vient du type des données surement...

Si vous avez besoin de plus d'explications n'hésitez pas à me le dire, c'est la première fois que je demande de l'aide pour du VBA donc pas habitué à m'expliquer !

Merci d'avance !

ChTi160 · 25 Juin 2013

Re : VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Bonsoir Zish
Bonsoir le fil
Bonsoir le forum

ne peux tu mettre un fichier exemple avec quelques lignes de données Non confidentielles ?
tu travailles sur un seul fichier ?
Merci Pour eux
Bonne fin de Soirée
Amicalement
Jean marie

Zish · 25 Juin 2013

Re : VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Bonsoir Jean Marie,

Je travaille sur plusieurs fichier, du moins pour la partie concernant ce code je vais chercher les valeurs dans un autre classeur, que je n'ai pas avec moi actuellement, je tacherais de le récupérer et de vous en mettre un morceau en pièce jointe.

ChTi160 · 25 Juin 2013

Re : VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Re
Ok !!!!!
Donc tu vas via tes macros chercher des données dans un autres classeurs , est il ouvert ce deuxième claseur?
Dans l'attente
Bonne fin de Soirée
Amicalement
Jean marie

Zish · 25 Juin 2013

Re : VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

J'ouvre le classeur via ma macro oui.
Voilà le code dans sa globalité si ca peut aider.

La boucle While à la fin est celle que j'essaye de supprimer pour remplacer par la boucle For...Next avec des variables.

Code:

Sub MAJ_CADENCIERS()

Dim tablodlcfab As Range
Dim tablodero As Range


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheets("MAJ DLC CAD").Select

Range("A2:O3000").ClearContents

Workbooks.Open Filename:="G:\planif\ANTHONY\A UTILISER\liste produits grossistes dero.xlsx"

datecrea = FileDateTime("G:\COPILOTE V3\Extraction cadenciers.xls")

Workbooks.Open Filename:="G:\COPILOTE V3\Extraction cadenciers.xls"


Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

Columns("A:B").Copy

Windows("ORDO QUOT.xlsm").Activate
Sheets("MAJ DLC CAD").Select
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Columns("A:B").Range("$A$1:$B$8000").RemoveDuplicates Columns:=Array(1, 2), _
    Header:=xlYes

fin = Range("A65536").End(xlUp).Row

'Set tablocode = Workbooks("Extraction cadenciers.xls").Sheets("CONTRATS DATES ARTICLES PAR REG").Range("A1:A10000")
'Set tabloclient = Workbooks("Extraction cadenciers.xls").Sheets("CONTRATS DATES ARTICLES PAR REG").Range("D1:D10000")
Set tablodlcfab = Workbooks("Extraction cadenciers.xls").Sheets("CONTRATS DATES ARTICLES PAR REG").Range("A1:E10000")
'Set tablodlcmin = Workbooks("Extraction cadenciers.xls").Sheets("CONTRATS DATES ARTICLES PAR REG").Range("F1:F10000")
Set tablodero = Workbooks("liste produits grossistes dero").Sheets("Feuil1").Range("A1:C10000")

Dim tablo1 As String
Dim tablo2 As String
Dim tablo3 As String

'Dim code As String
'Dim client As String

tablo1 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$A$1:$A$10000"
tablo2 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$D$1:$D$10000"
tablo3 = "[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG!$F$1:$F$10000"

Range("C2").Select
'On Error Resume Next
For i = 3 To fin
    code = Cells(i, 1).Value
    Cells(i, 3).Value = Application.WorksheetFunction.VLookup(code, tablodlcfab, 5, False)
    Cells(i, 13).Value = Application.WorksheetFunction.VLookup(code, tablodero, 3, False)
    For j = 4 To 12
        client = Cells(1, j).Value
        Cells(i, j).Formula = Application.Evaluate("=SumProduct((" & tablo1 & "=" & code & ") * (" & tablo2 & "=" & client & ") * (" & tablo3 & ")))")
    Next j
    Cells(i, 14).Value = Application.WorksheetFunction.Min(Range(Cells(i, 4), Cells(i, 13)))
    Cells(i, 15).Value = Application.WorksheetFunction.Max(Range(Cells(i, 4), Cells(i, 13)))
Next i

'On Error GoTo 0


'Range("C2").Select
'While ActiveCell.Offset(0, -2).Value <> ""
'    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!C1:C5,5,FALSE)"
    
'    ActiveCell.Offset(0, 1).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

'    ActiveCell.Offset(0, 2).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

'    ActiveCell.Offset(0, 3).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"
        
'    ActiveCell.Offset(0, 4).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"
    
'    ActiveCell.Offset(0, 5).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

'    ActiveCell.Offset(0, 6).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

'   ActiveCell.Offset(0, 7).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"

'    ActiveCell.Offset(0, 8).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"
    
'    ActiveCell.Offset(0, 9).FormulaR1C1 = _
        "=IF(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))=0,"""",(SUMPRODUCT(('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C1:R8000C1=RC1)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C4:R8000C4=R1C)*('[Extraction cadenciers.xls]CONTRATS DATES ARTICLES PAR REG'!R2C6:R8000C6))+1))"
    
'    ActiveCell.Offset(0, 10).FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-12],'[liste produits grossistes dero.xlsx]Feuil1'!C1:C3,3,FALSE))=TRUE,"""",IF(VLOOKUP(RC[-12],'[liste produits grossistes dero.xlsx]Feuil1'!C1:C3,3,FALSE)="""","""",VLOOKUP(RC[-12],'[liste produits grossistes dero.xlsx]Feuil1'!C1:C3,3,FALSE)))"
    
'    ActiveCell.Offset(0, 11).FormulaR1C1 = "=MIN(RC[-10]:RC[-1])"

'    ActiveCell.Offset(0, 12).FormulaR1C1 = "=MAX(RC[-11]:RC[-2])"
    
'    ActiveCell.Offset(1, 0).Select
'Wend

'Columns("C:O").Copy
'Columns("C:O").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Windows("Extraction cadenciers.xls").Close False

Windows("liste produits grossistes dero.xlsx").Close False

Windows("ORDO QUOT.xlsm").Activate
Sheets("MAJ DLC CAD").Select

Range("R2").FormulaR1C1 = "Macro exécutée le " & Date
Range("R3").FormulaR1C1 = "Date extraction du fichier Extraction cadenciers : " & datecrea

Range("A1").Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Grossièrement, je cherche à remplir les colonnes Grossistes et GMS ci dessous grâce à la formule sommeprod :

Code	DLC FAB	GROSSISTE	GMS
8010151	26	?	?
8010152	23	?	?

Car j'ai le fichier Extraction Cadencier qui est présenté de cette façon :

Code	Client	DLC MIN
8010151	GROSSISTE	12
8010151	GMS	10
8010152	GROSSISTE	11
8010152	GMS	13

En effet, je dois cumuler une recherche sur le code du produit et sur le type de client pour récupérer ensuite son nombre de jour correspondant. J'ai 9 colonnes à remplir (parce que 9 clients différents) et le fichier d'extraction compte plusieurs milliers de lignes.
La formule Sommeprod fonctionne parfaitement dans la boucle While, mais ma transcription avec des variables ne veut pas fonctionner...

Zish · 26 Juin 2013

Re : VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Bonjour,

Je me permet de remonter ce topic, je suis toujours rendu au point mort, pas moyen de faire marcher ma formule.

Effectuez une recherche sur Excel Downloads...

VBA Evaluate SUMPRODUCT Incompatibilité de type ou #VALEUR!

Zish

XLDnaute Nouveau

ChTi160

XLDnaute Barbatruc

Zish

XLDnaute Nouveau

ChTi160

XLDnaute Barbatruc

Zish

XLDnaute Nouveau

Zish

XLDnaute Nouveau

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums