Garder une valeur entre worksheet_change et module

Bonjour,

J'aimerai savoir pourquoi la valeur de ynoi devient vide après Call

Merci pour votre aide

Dim NOI As Range
Dim ou As Range
Dim qtedmd, qterst
Public ynoi
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ynoi = Rows("1:1").Find("NOI", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False).Column
qtedmd = Rows("1:1").Find("Qté dmd", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False).Column
qterec = Rows("1:1").Find("Qté reçue", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False).Column
qterst = Rows("1:1").Find("Qté reste", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False).Column

If Target.Column = ynoi Then Call ajoutnoi

If Target.Column = qtedmd Then Cells(Target.Row, qterst).Value = Cells(Target.Row, qtedmd).Value - Cells(Target.Row, qterec).Value

If Target.Column = qterec Then Cells(Target.Row, qterst).Value = Cells(Target.Row, qtedmd).Value - Cells(Target.Row, qterec).Value

Application.EnableEvents = True
End Sub
------------------------------------------------------------------------------------------------------------
Sub ajoutnoi()
cherche = Cells(Target.Row, ynoi).Value
    x = WorksheetFunction.CountIf(Worksheets("Base NOI").Range("a:a"), cherche)
    If x = 1 Then
        Set ou = Worksheets("Base NOI").Range("a:a").EntireColumn
        Set NOI = ou.Find(cherche, ou.Range("a1"), , xlWhole, xlByRows, xlNext, False)
        nomnoi = NOI.Offset(0, 1).Value
        Cells(Target.Row, ynoi + 1).Value = nomnoi
        Cells(Target.Row, ynoi + 2).Select
    Else
        Arr = Array("\\\", "Liste NOI.xlsx", "Rapport 1")
        Set c = Cells(Target.Row, ynoi + 1)
        With GetObject(Arr(0) & Arr(1))
            i = Application.IfError(Application.Match(cherche, .Sheets(Arr(2)).Columns(1), 0), "?")
            If IsNumeric(i) Then
                c.Value = .Sheets(Arr(2)).Cells(i, 2).Value
                c.Resize(, 10).Value = .Sheets(Arr(2)).Cells(i, 2).Resize(, 10).Value

                NomFeuille = ActiveSheet.Name
                Sheets("Base NOI").Select
                a = Range("A" & Rows.Count).End(xlUp).Row + 1
                Cells(a, 1).Value = .Sheets(Arr(2)).Cells(i, 1).Resize(, 3).Value

                Range(Cells(1, 1), Cells(a, 4)).Select
                ActiveWorkbook.Worksheets("Base NOI").Sort.SortFields.Clear
                ActiveWorkbook.Worksheets("Base NOI").Sort.SortFields.Add Key:=Range(Cells(2, 1), Cells(a, 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                With ActiveWorkbook.Worksheets("Base NOI").Sort
                    .SetRange Range(Cells(1, 1), Cells(a, 2))
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                Range("A2").Select
                Sheets(NomFeuille).Select
                Cells(Target.Row, ynoi + 2).Select
            Else
                MsgBox "NOI introuvable"
            End If
        .Close 0
        End With
    End If
End Sub

bonjour Progeric,

c'est la place de votre "Public ynoi", je suppose quand vous le déplacer vers un module normal, par exemple "module 1" ou un autre existant (et supprimer dans ce module d'une feuille) que cela fonctionnera.

autrement ajouter votre fichier

Non, cela ne fonctionne pas mieux

et donc ....

Bonjour à tous,

Sachez aussi que la variable Target de Private Sub Worksheet_Change(ByVal Target As Range) est une variable locale. Target n'est connue qu'au sein de la procédure Worksheet_Change.

Quand dansWorksheet_Change vous appelez ajoutnoi, la procédure ajoutnoi ne connaitra pas Target. Target sera considéré comme faisant référence à un variant non initialisée.

Il faut (d'après moi) minimiser au maximum le nombre de variables publiques.

Je préfère également utiliser "Option Explicit" en début de tout module. Cette option demande à ce que toutes les variables soient déclarées avant leur utilisation. Avec cette option, vous auriez obtenu une erreur à la compilation indiquant que Target n'est pas connu de ajoutnoi.

Pour minimiser le nombre de variables publiques, on peut définir sub ajoutnoi avec des paramètres et on appellera cette procédure avec les bons arguments. Un exemple :

• définir ajoutnoi par ajoutnoi(ByVal TargetLig, ByVal TargetCol, ByVal NoiCol)
• et depuis Worksheet_Change l'appeler avec : Call ajoutnoi(Target.Row, Target.Column, ynoi)

...

Ce n'est pas pour vous embêter qu'on demande un fichier représentatif et anonymisé mais pour que les réponses soient plus pertinentes.

...

Merci cela fonctionne très bien