VBA recherche valeur Max d'une colonne

[david84]

Bonjour,
La macro placée dans le fichier ne me détecte pas la valeur max de la colonne B comme je le voudrais. Cependant je ne vois pas où est l'erreur.
Merci de me donner votre solution et de m'expliquer mon erreur.
A+

 

[CBernardT]

Re : VBA recherche valeur Max d'une colonne

Bonjour David,

C'est la définition de la plage active en colonne B qui pose problème. Essaye la syntaxe suivante :

Sub Max()
Dim MyRange As Range, Reponse As String
With Sheets("feuil1")
Set MyRange = .Range("B2:B" & .Range("B65536").End(xlUp).Row)
Reponse = Application.WorksheetFunction.Max(MyRange)
MsgBox "la valeur max est " & Reponse
End With
End Sub

 

[mth]

Re : VBA recherche valeur Max d'une colonne

Bonjour david

Un .Range("B2") en trop je crois, essaie en remplaçant cette ligne:

Set myrange = Worksheets("feuil1").Range("B2", Range("B2").End(xlDown))

Bonne journée

m

Edit: Oups .... Bonjour CBernardT
à la bourre moi .....
Bonne journée
m

 

[Manu62]

Re : VBA recherche valeur Max d'une colonne

Bonjour,

Ci dessous le code modifier
Sub test()
Dim myrange As Range
Dim reponse As String
Set myrange = Worksheets("feuil1").Range("b1:b65000")
reponse = Application.WorksheetFunction.Max(myrange)
MsgBox "la valeur max est " & reponse
End Sub


A+

 

[david84]

Re : VBA recherche valeur Max d'une colonne

Re
CBernardT : la macro plantait (il m'affiche "erreur de compilation....").
En modifiant

Set MyRange = .Range("B2:B" & .Range("B65536").End(xlUp).Row)

par

Set myrange = Range("B2:B" & Range("B65536").End(xlUp).Row)

donc en enlevant les "." cela fonctionne (attention, j'ai testé directement sur mon fichier d'origine et pas sur le fichier test ci-joint !).
Sais-tu pourquoi ?

m : cela fonctionne correctement.
Merci à vous 2 pour vos solutions.
A+

 

[mromain]

Re : VBA recherche valeur Max d'une colonne

Bonjour le fil, David

Juste un mini détail : vu que tu travailles sur 2007, tu risque avoir un soucis avec ces codes si tu as plus de 65536 lignes (c'est vrai, c'est plutôt rare...).
Mais pour être sur de ne pas tomber sur cette limitation, je te propose ce code :

Sub Max()
Dim MyRange As Range, Reponse As String
    With Sheets("feuil1")
        Set MyRange = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End With
    Reponse = Application.WorksheetFunction.Max(MyRange)
    MsgBox "la valeur max est " & Reponse
End Sub

PS: je l'ai précisé au début, c'est vraiment un mini-détail. Certains appelleront ça de "l'enculage de mouche"

a+

 

[david84]

Re : VBA recherche valeur Max d'une colonne

Re
merci à mromain et à Manu dont les propositions fonctionnent.
mromain : ta proposition fonctionne sur le fichier test mais pas telle quelle sur le fichier original (même remarque qu'à CBernardT dont j'ai testé la macro sur le fichier test et pas de problème,, à savoir que je dois enlever des"." pour que cela ne plante pas.
Pour plus de clarté, je vous joins un extrait de mon code original où j'ai dû enlever les "." après

.Offset(1, -1) = Application.WorksheetFunction.Max

:

Sheets("création").Range("E6:E17").Copy
    With Sheets("BD").Range("C65000").End(xlUp).Rows
    .Offset(1, 0).PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    .Offset(1, 12).FormulaR1C1 = Date
    '.Offset(1, -1) = Application.WorksheetFunction.Max(Range("B2", Range("B2").End(xlDown)))proposition mth
    [B].Offset(1, -1) = Application.WorksheetFunction.Max(Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row))

[/B]
j'ai également placé la proposition de m au dessus de la tienne avec une ' qui fonctionne aussi.


C'est dû à quoi à votre avis ?
A+

 

[CBernardT]

Re : VBA recherche valeur Max d'une colonne

Re,

Si la macro fonctionne sur le fichier test et non sur le fichier réel, j'y vois deux erreurs possibles :

1- Une erreur banale de transcription, toujours possible ;

2- Une erreur de syntaxe, plus probable, correspondant à une mauvaise adaptation au fichier réel.

A noter que les "." sont là pour lier la plage à une feuille. Dans le test, la feuille est "Feuil1", peut-être que dans le fichier réel, la désignation est différente ?

 

[mth]

Re : VBA recherche valeur Max d'une colonne

re

David, je dis peut-être une grosse bêtise, mais j'ai l'impression que l'on est en train de se mélanger les pinceaux entre les deux méthodes.

Soit on utilise le With /End with, et dans ce cas là, pas la peine de répéter le nom de l'onglet puisqu'il est défini dans le With, en revanche il faut des points :

With Sheets("Feuil1")
  Set myrange = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With

Soit on n'utilise pas le With/End With, du coup on précise le nom de l'onglet dans l'instruction, il ne faut donc plus de points:

Set myrange = Sheets("Feuil1").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

Voilà ce que je comprends, mais je suis avec attention la suite car je ne suis pas sûre.

@ +

mth

 

[Cousinhub]

Re : VBA recherche valeur Max d'une colonne

Bonjour le Fil...

Ma petite contribution...

Dans ton code, David, tu fais référence à :

With Sheets("BD").Range("C65000").End(xlUp).Rows

Donc il est normal que les . devant Range provoque une erreur, ces cellules ne faisant pas partie de ta zone de référence...

Tu pourrais corriger ainsi :

    With Sheets("BD")
        With .Range("C65000").End(xlUp).Rows
            .Offset(1, 0).PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True
            .Offset(1, 12).FormulaR1C1 = Date
        End With
        '.Offset(1, -1) = Application.WorksheetFunction.Max(Range("B2", Range("B2").End(xlDown)))proposition mth
        .Range("C65000").End(xlUp).Rows.Offset(1, -1) = Application.Max(.Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row))
    End With

Bonne journée à toutes et à tous

 

[CBernardT]

Re : VBA recherche valeur Max d'une colonne

Re,

Bonjour mth,

Je cite :

Set myrange = Sheets("Feuil1").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

Attention, cette syntaxe n'est pas totalement correcte :

La plage est bien définie, elle est liée à une feuille, par contre, la définition de la dernière ligne n'est liée à aucune feuille, chose pouvant entraîner une possible erreur en cas de changement de feuille !

Correction :

Set myrange = Sheets("Feuil1").Range("B2:B" & Sheets("Feuil1").Range("B" & Rows.Count).End(xlUp).Row

 

[Cousinhub]

Re : VBA recherche valeur Max d'une colonne

Re-,

Le code (à tester) un peu simplifié :

Dim DerLig As Long
Sheets("création").Range("E6:E17").Copy
With Sheets("BD")
    DerLig = .Range("C65000").End(xlUp).Row + 1
    With .Cells(DerLig, 3)
        .PasteSpecial Paste:=xlValue, Transpose:=True
        .Offset(, 12).Value = Date
    End With
    .Cells(DerLig, 2) = Application.Max(.Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row))
End With

Bonne journée

 

[mth]

Re : VBA recherche valeur Max d'une colonne

re

Bon, du coup j'ai un peu l'impression de squatter le fil de David o tu m'en veux pas ? ), mais voilà qui m'aide à structurer un peu mes tatonnements empiriques, donc pardon à David, mais un très grand MERCI bhbh et CBernardT pour vos commentaires

@ ++

mth

 

[david84]

Re : VBA recherche valeur Max d'une colonne

Re
bhbh

Dim DerLig As Long
Sheets("création").Range("E6:E17").Copy
With Sheets("BD")
DerLig = .Range("C65000").End(xlUp).Row + 1
With .Cells(DerLig, 3)
.PasteSpecial Paste:=xlValue, Transpose:=True
.Offset(, 12).Value = Date
End With
.Cells(DerLig, 2) = Application.Max(.Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row))
End With

Bon, dis-moi si j'ai compris et n'hésite pas à me corriger et à m'apporter des précisions parce qu'il y a des infos que je n'ai pas comprises au niveau de la syntaxe :le fait de placer le "." évite de reprendre le nom de la feuille à laquelle on fait référence. Donc :

With Sheets("BD")

: la feuille BD est sélectionnée

DerLig = .Range("C65000").End(xlUp).Row + 1

on définit la 1ère ligne vide de cette feuille

With .Cells(DerLig, 3)
.PasteSpecial Paste:=xlValue, Transpose:=True
.Offset(, 12).Value = Date
End With

au sein de cette 1ère ligne vide, on sélectionne la cellule de la colonne C à partir de laquelle on colle les valeurs copiées (+ la date).

.Cells(DerLig, 2) = Application.Max(.Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row))

Là, tu sors de la référence liée à la 3ème cellule (end with), tu te "rattaches" à nouveau à à la feuille sélectionnée et tu fais référence à la 2ème cellule de la ligne.
Est-ce cela ?

CBernardT
merci pour l'info concernant la syntaxe car je ne le savais pas

mth

j'ai un peu l'impression de squatter le fil de David ( tu m'en veux pas ? )

Pas de soucis car tout le monde profite des infos (et plus on est de fous...).
Moralité : merci à tous et concernant le VBA, c'est pas gagné !
A+

 

[Cousinhub]

Re : VBA recherche valeur Max d'une colonne

Re-,

Tu as parfaitement compris...

Tu as vu qu'on peut imbriquer plusieurs With...

Le premier référencie à la feuille

Le deuxième, à la première cellule vide de la colonne C

Tu peux également insérer un autre With complètement indépendant, du style :

Dim DerLig As Long
Dim DerCol As Integer
Sheets("création").Range("E6:E17").Copy
With Sheets("BD")
    DerLig = .Range("C65000").End(xlUp).Row + 1
    With .Cells(DerLig, 3)
        .PasteSpecial Paste:=xlValue, Transpose:=True
        .Offset(, 12).Value = Date
        With Sheets("AutreFeuille")
            DerCol = .Range("IV1").End(xlToLeft).Column
        End With
    End With
    .Cells(DerLig, 2) = Application.Max(.Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row))
End With

Ici, je calcule la dernière colonne de la feuille "AutreFeuille", le With est complétement indépendant des 2 autres au-dessus...

Pour bien de repérer, que ce soit dans les boucles ou autres, n'oublie pas l'indentation, comme dans mon code, cela te permet de lire facilement tes codes (comme s'il y avait des tabulations...)

Bon courage dans ton apprentissage...(mais tu sais où trouver de l'aide...)

Bonne soirée