[VBA] définition plage de données

Question

Bonjour,
Voici ma fonction:

Function MaSommeSi(Col1 As String, Code As String, Col2 As String) As Integer
Dim Pla1, Pla2 As Range
Dim Chem, Fich, Ong As String
Dim Wb As Workbook

Chem = Range("Chemin").Cells
Fich = Range("fichier").Cells
Ong = Range("onglet").Cells
Set Wb = Chem & Fich

Pla1 = Wb.Worksheets(Ong).Columns(Col1 & " : " & Col1)
Pla2 = Wb.Worksheets(Ong).Columns(Col2 & " : " & Col2)

MaSommeSi = Application.WorksheetFunction.SumIf(Pla1, Code, Pla2)

End Function

Dans le fichier où j'utilise la fonction, j'ai nommé 3 cellules (chemin, fichier et onglet). Le but est de pouvoir modifier le nom du fichier sur lequel on fait la somme.si.
Et ça ne fonctionne pas... je pense que c'est la définition des Pla1 et Pla2 qui sont fausses.

Merci de votre aide!

thev · Answer

Chem & Fich n'est pas une variable de type classeur. Il faut mettre  Set Wb = Workbooks(Chem & Fich )  au lieu de Set Wb = Chem & Fich 

D'autre part  la propriété Columns(Col1 & " : " & Col1)  n'existe pas , la bonne propriété est  Columns( " Col1"); il faut la remplacer par la propriété Range . Ceci devrait être mieux :

Set Pla1 = Wb.Worksheets(Ong).Range(Col1 & " : " & Col1)
Set Pla2 = Wb.Worksheets(Ong).Range(Col2 & " : " & Col2)

sélina · Answer

merci thev
...mais j'ai essayé, ça ne fonctionne toujours pas!

thev · Answer

Il y a des espaces en trop 

Set Pla1 = Wb.Worksheets(Ong).Range(Col1 & ":" & Col1)
Set Pla2 = Wb.Worksheets(Ong).Range(Col2 & ":" & Col2)

sélina · Answer

c'est vrai...
mais ça ne fonctionne toujours pas! :(

thev · Answer

Function MaSommeSi(Col1 As String, Code As String, Col2 As String) As Integer
Dim Pla1, Pla2 As Range
Dim Chem, Fich, Ong As String
Dim Wb As Workbook

On Error GoTo erreur_fonction
Chem = Range("Chemin").Cells & "\"
Fich = Range("fichier").Cells
Ong = Range("onglet").Cells

' recherche du fichier si chargé en mémoire
For Each Wb In Workbooks
    If Wb.Name = Fich Then Exit For
Next Wb
' -----------------------------------------
' si fichier non chargé en mémoire, on procède à son ouverture ------
If Wb Is Nothing Then
    Set wb_save = ActiveWorkbook      'sauvegarde du classeur activé
    Workbooks.Open (Chem & Fich)
    Set Wb = ActiveWorkbook
    wb_save.Activate                  'réactivation du classeur sauvegardé
End If
' -------------------------------------------------------------------------------

Set Pla1 = Wb.Worksheets(Ong).Columns(Col1 & ":" & Col1)
Set Pla2 = Wb.Worksheets(Ong).Columns(Col2 & ":" & Col2)

MaSommeSi = Application.WorksheetFunction.SumIf(Pla1, Code, Pla2)
Exit Function

erreur_fonction:
    MsgBox Err.Description

End Function

thev · Answer

La réponse est oui. 
Dans le code, j'ai rajouté un message d'erreur.  Quel est-il ?

sélina · Answer

il n'y a pas de message d'erreur qui apparait, j'ai juste #VALEUR! dans la cellule.

thev · Answer

si par exemple col1 = colonne A, col2 = colonne B, code = 1, il faut entrer =MaSommeSi("A";1;"B")

par ailleurs, il faut charger le fichier en mémoire car l'ouverture du fichier ne se fait pas en mode fonction. Donc modifier le code ainsi

Function MaSommeSi(Col1 As String, Code As String, Col2 As String) As Integer
Dim Pla1, Pla2 As Range
Dim Chem, Fich, Ong As String
Dim Wb As Workbook

On Error GoTo erreur_fonction
Chem = Range("Chemin").Cells & "\"
Fich = Range("fichier").Cells
Ong = Range("onglet").Cells

' recherche du fichier si chargé en mémoire
For Each Wb In Workbooks
    If Wb.Name = Fich Then Exit For
Next Wb
If Wb Is Nothing Then
    MsgBox "erreur : classeur " & Fich & " non chargé"
    Exit Function
End If

Set Pla1 = Wb.Worksheets(Ong).Columns(Col1)
Set Pla2 = Wb.Worksheets(Ong).Columns(Col2)

MaSommeSi = Application.WorksheetFunction.SumIf(Pla1, Code, Pla2)
Exit Function

erreur_fonction:
    MsgBox Err.Description

End Function

sélina · Answer

ca fonctionne!!!! :)

Je ne mettais pas les guillemets dans la formule...
=MaSommeSi(A;1;B)
au lieu de =MaSommeSi("A";1;"B")

Merci beaucoup pour la réactivité dans les réponses.

sélina · Answer

par contre...
si je change des valeurs, la somme ne se met pas à jour automatiquement comme une autre formule excel de base.
Est ce normal?

eriiic · Answer

Bonsoir,

ajoute application.volatile au début de ta fonction.
eric

thev · Answer

pour tenir compte de l'apport d'Eriic, le code doit donc être modifié ainsi :

Function MaSommeSi(Col1 As String, Code As String, Col2 As String) As Integer

Application.Volatile

Dim Pla1, Pla2 As Range
Dim Fich, Ong
Dim Wb As Workbook
Dim nom As Name
On Error GoTo erreur_fonction

For Each nom In ActiveWorkbook.Names
    If nom.Name = "fichier" Then Fich = Range("fichier").Cells
    If nom.Name = "onglet" Then Ong = Range("onglet").Cells
Next nom
    If IsEmpty(Fich) Then Fich = ActiveWorkbook.Name
    If IsEmpty(Ong) Then Ong = ActiveSheet.Name

' recherche du fichier si chargé en mémoire
For Each Wb In Workbooks
    If Wb.Name = Fich Then Exit For
Next Wb
If Wb Is Nothing Then
    MsgBox "erreur : classeur " & Fich & " non chargé"
    Exit Function
End If

Set Pla1 = Wb.Worksheets(Ong).Columns(Col1)
Set Pla2 = Wb.Worksheets(Ong).Columns(Col2)

MaSommeSi = Application.WorksheetFunction.SumIf(Pla1, Code, Pla2)
Exit Function

erreur_fonction:
    MsgBox Err.Description

End Function

eriiic · Answer

Re,

Ou bien passer col1 et col2 en plage1 as range et plage2 as range.
Ca présente en plus l'avantage de pouvoir désigner les colonnes à la souris.
eric

sélina · Answer

Lorsque je modifie une valeur dans le fichier où je recherche mes données, j'obtiens le message d'erreur "L'indice n'appartient pas à la sélection"

eriiic · Answer

Re,

et tu veux à tout prix une fonction, personnalisée ?
Sinon tu écris le nom de ton classeur entre [ ] en A1, et en A2 :
=SOMME.SI(INDIRECT(A1&"Feuil1!$A:$A");"=2";INDIRECT(A1&"Feuil1!$B:$B"))

[VBA] définition plage de données

15 réponses

Discussions similaires

Newsletters