[VBA] définition plage de données

Fermé
sélina - 20 mai 2009 à 19:01
 sélina - 24 mai 2009 à 19:55
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!

15 réponses

eriiic Messages postés 24571 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 8 mai 2024 7 216
24 mai 2009 à 14:16
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"))
1
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
20 mai 2009 à 22:59
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)
0
merci thev
...mais j'ai essayé, ça ne fonctionne toujours pas!
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
21 mai 2009 à 10:55
Il y a des espaces en trop

Set Pla1 = Wb.Worksheets(Ong).Range(Col1 & ":" & Col1)
Set Pla2 = Wb.Worksheets(Ong).Range(Col2 & ":" & Col2)
0

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question
c'est vrai...
mais ça ne fonctionne toujours pas! :(
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
21 mai 2009 à 18:29
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
0
ça ne fonctionne toujours pas...
mais je ne comprends pas, on peut mettre des boucles dans une fonction utilisé dans excel directement (pas dans une macro je veux dire...)?
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
23 mai 2009 à 20:24
La réponse est oui.
Dans le code, j'ai rajouté un message d'erreur. Quel est-il ?
0
il n'y a pas de message d'erreur qui apparait, j'ai juste #VALEUR! dans la cellule.
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
23 mai 2009 à 22:17
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
 
0
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.
0
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?
0
eriiic Messages postés 24571 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 8 mai 2024 7 216
23 mai 2009 à 23:18
Bonsoir,

ajoute application.volatile au début de ta fonction.
eric
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
24 mai 2009 à 00:39
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
0
eriiic Messages postés 24571 Date d'inscription mardi 11 septembre 2007 Statut Contributeur Dernière intervention 8 mai 2024 7 216
24 mai 2009 à 11:05
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
0
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"
0
thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024 683
24 mai 2009 à 16:52
As-tu bien remplacé Dim Chem, Fich, Ong As String par Dim Chem, Fich, Ong ??

le "as String" est à supprimer.
0
sélina > thev Messages postés 1854 Date d'inscription lundi 7 avril 2008 Statut Membre Dernière intervention 3 mai 2024
24 mai 2009 à 19:55
Non effectivement, je n'avais pas enlevé le "as string"... merci thev maintenant ça fonctionne nickel!
0