Discussion :

[Tintin123]

Hello le Forum,

J'ai un soucis avec une macro en recherche V que je ne souhaite effectuer que si la condition precedente n'est realise (si la value d'une cellule est 1).

Je procède en faisant une boucle mais celle ci ne semble pas marcher.

Aussi, pour avoir un fichier plus performant je souhaiterai ne voir apparaitre que le résultat et non pas la formule dans la colonne. J'ai essayé de faire en passant par la sauvegarde du résultat dans une variable que j'avais déclaré mais ca bugg.....

Le code que j'ai pour l instant est le suivant

Dim i As Long
Dim LR As Long

Sub BATCH()

LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]=1,-VLOOKUP(R[1]C[-32],Sheet6!C1:C9,3,0),0)"
Range("AK3").Select
Next i

End Sub


Merci

[illight]

2 questions :
- pourquoi tu le fais en VBA, et pas dans une formule directement dans ton fichier ?
- si tu ne veux pas que la formule s'affiche, tu as 2 solutions :
- soit tu fais ta recherche en VBA direct, sans passer par une formule
- soit tu utilises ta formule, mais si tu veux qu'elle disparaisse, tud ois faire un copier/coller valeurs sur tes cellules qui ont la formule, sinon elle apparaitra à chaque fois


En espérant t'avoir guidé vers la solution

[Tintin123]

Hello Illight

Je le fais initialement dans le fichier excel directement mais celui ci devient trop lourd du coup je veux passer par VBA pour avoir directement les valeurs et alleger le fichier

L option de copier coller les valeurs n'est pas pratique car c est un fichier que je veux mettre a jours toutes les semaines....

une idée pour resoudre mon probleme de code??

Merci

[illight]

L option de copier coller les valeurs n'est pas pratique car c est un fichier que je veux mettre a jours toutes les semaines....

Je comprend pas ta réponse : étant donné que tu vas mettre la formule à chaque fois par ta macro, si tu fais un copier/coller valeurs par la suite( pour enlever les formules), même si tu refais toutes les semaines ça changera rien

[Tintin123]

l objectif de la macro et de VBA est quand meme de ne pas faire d'operation manuelles...

Je peux faire uen macro pour copier coller mais le probleme que je rencontre dans un premier temps et que la loop que je fais avec le next i ne marche pas....

[illight]

c'est normal, ton i tu ne l'utilise nulle part dans ta boucle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub BATCH()
 
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
ActiveCell.FormulaR1C1 = _
"=IF(RC[-6]=1,-VLOOKUP(R[1]C[-32],Sheet6!C1:C9,3,0),0)"
Range("AK3").Select
Next i
 
End Sub

Dans ton IF, il n'y a pas la variable i..

par ailleurs, pourquoi ne pas utiliser le "Formula" plutôt que FormulaR1C1, qui n'est pas très pratique ici...

[Tintin123]

J ai modifie le code en intregrant le boucle avec le "i", mais c est encore pire....

Dim i As Long
Dim LR As Long

Sub BATCH()

LR = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LR
ActiveCell.FormulaR1C1 = _
"=IF(cells(i,31).value =1,-VLOOKUP(cells(i,5),Sheet6!C1:C9,3,0),0)"
Cells(i, 37).Select
Next i

End Sub



Je ne sais pas comment utiliser le "formula" uniquement, quelqu 'un peu m aiguiller?
Merci

[Tintin123]

J 'ai retravaillé le code mais j ai comme resultat sur les cellule #name?

Quelqu'un a une idée????

Dim i As Long
Dim LR As Long

Sub BATCH()

LR = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LR
ActiveCell.Formula = _
"=IF(cells(i,31) =1,-VLOOKUP(cells(i,5),Sheet6!C1:C9,3,0),0)"
Cells(i, 37).Select
Next i

End Sub

[illight]

N'oublie aps de mettre les balise CODE, ça sera déjà plus lisible.

Dans un 2e temps, le "-" devant ton VLOOKUP c'est normal ?

[Tintin123]

oui le "-" est normal puisque je veux prendre la valeur qui est dans le range de recherche, mais la mettre en négatif.

Le code actuel me donne toujours ce résultat que je ne comprend pas #name?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim i As Long
 Dim LR As Long
 
Sub BATCH()
 
LR = Range("B" & Rows.Count).End(xlUp).Row
 
For i = 2 To LR
 ActiveCell.Formula = _
 "=IF(cells(i,31) =1,-VLOOKUP(cells(i,5),Sheet6!C1:C9,3,0),0)"
 Cells(i, 37).Select
 Next i
 
End Sub

[illight]

C'est justement à cause de ton "-" enfin je pense

Tu essayé de mettre ta formule dans ta cellule directement voir si elle fonctionnait ?

Si tu écris comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub BATCH()
 
LR = Range("B" & Rows.Count).End(xlUp).Row
 
For i = 2 To LR
 ActiveCell.Formula = _
 "=IF(cells(i,31) =1,"""-""" & VLOOKUP(cells(i,5),Sheet6!C1:C9,3,0),0)"
 Cells(i, 37).Select
 Next i
 
End Sub

(je sais plus s'il faut 2 ou 3 guillemets pour le tiret..)
ça marche ?

[Tintin123]

Hello illight,

J'ai essayé les deux options (1 ou 2 "") mais cela ne marche pas.
Avec deux guillements j ai le meme message dans le resultat. Avec un guillements le code ne s'execute meme pas :%

[Philippe Tulliez]

Bonjour,
Pour écrire une formule sur une plage de cellule, il n'y a pas de raison d'utiliser une boucle ce qui ralenti l'exécution de la procédure surtout pour 10000 lignes.
La syntaxe est plage.formulaR1C1 = formule (pour les formules de style R1C1) ou plage.formula = formule
Exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub TestFormula()
 Dim rng As Range
 With ThisWorkbook.Worksheets("Feuil2")
  Set rng = .Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
 End With
 ' rng.FormulaR1C1 = "=LEN(RC[-2])" ' Pour style R1C1
 rng.Formula = "=LEN(B2)"
 Set rng = Nothing
End Sub

[Tintin123]

Bonjour Philippe,

Merci pour le coup de pouce,

En revanche j 'ai l'impression que cela ne resolve pas le probleme de resutat de ma recherche V ou alors je n'ai pas bien compris votre explication.

Avez-vous un autre tuyaux pour m'aider dans mon problème?

Merci

[illight]

Merci Philippe quelquechose que je ne savais pas

[Philippe Tulliez]

Bonjour,

En revanche j 'ai l'impression que cela ne resolve pas le probleme de resutat de ma recherche V ou alors je n'ai pas bien compris votre explication.
Avez-vous un autre tuyaux pour m'aider dans mon problème?

Quel est le problème exactement ?
Le temps d'exécution trop lent ?
Soit on passe par du code VBA pour faire la recherche en utilisant par exemple la fonction EVALUATE(Formule), Application.WorksheetFunction.VLookup ou une autre méthode de recherche mais cela implique alors de passer par une boucle.
Pas sûr que cela aille plus vite mais seul des tests permettront de déterminer quelle méthode est la plus rapide.

Si l'on revient à la solution de la formule qui doit garder le résultat final.
Outre la propriété ScreenUpdating à False, je placerais aussi la propriété Calculation à xlCalculationManual et après avoir copié la formule, remplacer la formule par son résultat.
Soit pour le même exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub TestFormula()
 Const myFormula As String = "=LEN(B2)"
 Dim rng As Range, ModeCalculation As Integer
 With Application
  ModeCalculation = .Calculation: .ScreenUpdating = False: .Calculation = xlCalculationManual
 End With
 With ThisWorkbook.Worksheets("Feuil2")
  Set rng = .Range("D2:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
 End With
 With rng
 .Formula = myFormula ' place la formule dans la plage
 .Worksheet.Calculate ' force le recalcul de la feuille contenant la formule
 .Value = .Value ' Transforme formule en valeur
 End With
 With Application: .ScreenUpdating = True: .Calculation = ModeCalculation: End With
 Set rng = Nothing
End Sub

[Tintin123]

Avant de travailler sur la rapidité d'exécution, je voudrais d'abord resoudre le problème du code qui me donne comme resulutat " #name?"

Mais je vais prendre tous les conseils par la suite car ils sont très pertinents et interessants

[Philippe Tulliez]

Bonjour,
Si la formule renvoie #name?, cela signifie qu'un nom n'est pas reconnu. Soit une formule, soit un nom (plage nommée ou cellule nommée) ou tout simplement une constante alphanumérique sans guillemets qui est interprété par excel comme pouvant être une formule ou un nom.
La meilleure manière de procéder pour éviter ce genre de désagrément est de tester la formule sans le VBA directement sur la plage concernée.
A partir du moment où l'on obtient le résultat escompté
1) On sélectionne la cellule contenant la formule testée de la première ligne
2) On enclenche l'enregistreur de macros
3) On se positionne dans la barre des formules et on tape sur la touche Entrée ou Enter.
4) On arrête l'enregistreur.
5) On récupère dans l'éditeur (VBE) la chaîne de caractères contenant la formule que l'on insère alors dans notre procédure initiale.
Ainsi on est certain que la formule est correcte.

[Tintin123]

Oui Philippe c est ce que j avais fait initialement et le code qui fonctionne et le suivant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub test()
 
 
    ActiveCell.FormulaR1C1 = _
        "=IF(RC31=1,-VLOOKUP(RC5,Sheet6!R1C1:R150000C8,3,0),0)"
    Range("AK3").Select
End Sub

je me suis enmelé les pinceaux par la suite lorsque je veux dupliquer la formule sur toute la colonne jusqu'à la dernière ligne et lorsque je veux remplacer par la formule qui apparait dans la cellule directement par le resultat....

[Philippe Tulliez]

Bonjour,
Tu t'es emmêlé les pinceaux parce-que tu as voulu l'intégrer dans une boucle et comme tu travailles en style R1C1 c'est encore plus compliqué.
Si tu utilises l'exemple que j'ai proposé, tu places ta formule telle quelle et c'est excel qui fera l'incrémentation des lignes.