Fonction SI + NB.SI Excel en VBA

Version imprimable

29/10/2010, 15h42
pomdeterfrite

Fonction SI + NB.SI Excel en VBA

Bonjour à tous !

Je cherche désespéremment à transcrire une formule avec un NB.SI en code VBA (afin d'alléger les délais d'ouverture car j'ai une comparaison qui se fait entre 2 fichiers différents sur 4000 lignes)

Voici la formule que je veux remplacer :

Code:

SI(NB.SI('[TdB.xls]Status'!$G:$G;Base!C5)>0;"x";"")

elle permet de trouver au moins un cas dans la plage G:G d'un autre fichier (TdB.xls) égal à la colonne C de mon fichier actuel et si c'est le cas je met un "x" dans la colonne O de mon fichier actuel.
29/10/2010, 15h48
momo93240

Essaye ça :

Code:

Sheets("ta feuil").Range("cellules ou ecrire la formule").FormulaLocal = "=SI(NB.SI('[TdB.xls]Status'!$G:$G;Base!C5)>0;'""x"";"""") "
29/10/2010, 16h10
pomdeterfrite

Malheureusement ca part dans un calcul interminable et ca plante ...

Mon fichier est déjà très lourd et j'aimerai me débarasser de cette formule et partir sur une boucle If avec test comme un NB.SI à l'intérieur mais je n'ai aucune idée de comment le faire

Salut,

Ce que tu souhaites c'est simplement le résultat dans ta feuille "Base" et pas la formule, n'est ce pas ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Sub compteur()
Dim Monfichier As Workbook, i As Integer
 
Set Monfichier = Workbooks("TdB.xls")
 
i = 5 'point de départ
 
For i = 5 To 4000
    If WorksheetFunction.CountIf(Monfichier.Sheets("Status").Columns(7), Sheets("Base").Cells(i, 3)) > 0 Then
' je te mets le résultat dans la 4ème colonne comme je ne sais pas où tu veux le mettre, soit la colonne "D"
 
        Sheets("Feuil1").Cells(i, 4) = "X"     
Else
        Sheets("Feuil1").Cells(i, 4) = ""
    End If
Next i
 
End Sub

Ca s'execute en 10 secondes sur 4000 lignes environ avec ma bécane bien sûr.
Si tu veux des renseignements n'hésite pas.
A+

29/10/2010, 18h10
pomdeterfrite

Super merci c'est exactement ce que je cherche !!!

Par contre il me dit " Erreur d'exécution 9 : L'indice n'appartient pas à la sélection".

Mon fichier est bien dans le meme dossier et j'ai vérifié les noms du fichier et des onglets

D'ou est ce que ca peut venir ?
29/10/2010, 18h14
will-1981

De rien,

quand tu clique sur déboguage il pointe quelle ligne ?

Et sinon n'oublie pas de changer le nom des feuilles là où tu veux écrire le résultat. remplace Feuil1 par Base.

Une petite question ton fichier ne varie pas en nombre de lignes ?

Je n'ai pas accès au déboguage, simplement "OK"

J'ai bien remplacé les noms de feuilles et mon nombre de lignes est fixe.
Le code a l'air nickel, je comprends pas d'ou vient cette erreur

Voici le code exact :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub compteur() Dim Monfichier As Workbook, i As Integer Set Monfichier = Workbooks("import.xls") i = 5 'point de départ For i = 5 To 4000 If WorksheetFunction.CountIf(Monfichier.Sheets("HL").Columns(7), Sheets("Base").Cells(i, 3)) > 0 Then Sheets("Base").Cells(i, 18) = "x" Else Sheets("Base").Cells(i, 18) = "" End If Next i End Sub

29/10/2010, 18h34
will-1981

Mets le code dans un module, u'est ce qui se passe ?
29/10/2010, 19h30
pomdeterfrite

Toujours la même chose ... Je viens de tester sous Excel 2010 j'ai le mode déboguage avec l'erreur cette fois.
Il me dit "MonFichier" = Nothing et c'est "import.xls" qui n'appartient pas à la sélection... reste à savoir ce que ca veut dire ...
29/10/2010, 19h53
Pierre Fauconnier

import.xls est ouvert?

Sinon, tu dois l'ouvrir
02/11/2010, 09h31
pomdeterfrite

Effectivement avec les 2 fichiers ouverts ca marche ! Mais j'aurai aimé que ca fonctionne sans ouvrir import.xls

Salut,

Dans ce cas utilise au début l'instruction suivante : par contre tu vas inscrire le nom du chemin dans la macro en dur, donc si tu souhaites changer le fichier source de place, il te faudra modifier le nom du chemin dans la macro.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
Sub compteur()
' n'oublie pas d'annuler le rafraichissement de l'écran en début de macro et de le réactiver à la fin, ça fait gagner du temps à l'éxcution avec cette application
Application.ScreenUpdating = False
 
Dim Monfichier As Workbook, i As Integer
 
'ouverture du fichier import.xls
    Workbooks.Open Filename:= " inscrire le nom du chemin (par exemple : Z:\Logistique\import.xls" 
 
Set Monfichier = Workbooks("import.xls")
 
i = 5 'point de départ
 
For i = 5 To 4000
    If WorksheetFunction.CountIf(Monfichier.Sheets("HL").Columns(7), Sheets("Base").Cells(i, 3)) > 0 Then
 
        Sheets("Base").Cells(i, 18) = "x"
Else
        Sheets("Base").Cells(i, 18) = ""
    End If
Next i
 
'Puis à la fin tu mets l'instruction suivante pour éviter de garder le fichier ouvert et d'enregistrer quelques modifications sur le fichier source
 
    Monfichier.Activate 'Normalement on évite d'activer des fichiers pour rien mais je ne sais pas comment faire autrement
 
    ActiveWorkbook.Close SaveChanges:=False 
 
Application.ScreenUpdating = True
 
End Sub

A toi de jouer ;)
Will

03/11/2010, 21h50
AlainTech

Il est possible de travailler sur des classeurs fermés.
Voir:
http://silkyroad.developpez.com/VBA/ClasseursFermes/
Attention! Bien lire toutes les explications depuis le début.
04/11/2010, 13h41
will-1981

Merci pour le lien AlainTech :ccool: mais j'ai une question sur le principe est ce que l'on peut travailler et modifier un fichier sans l'ouvrir.

Car moi j'ai toujours des modifications à faire avant de m'en servir comme une base de donnée.

A bientôt,
Will
04/11/2010, 13h50
pomdeterfrite

Merci !! :)