Ecrire dans la première cellule vide d'une colonne

Le 12/09/2018 à 11:23

Bonjour,

je suis un peu embêté car je coince sur une fonction qui semble être très simple, mais qui me pose problème...

J'ai une feuille Excel, sur laquelle j'ai une colonne (E), dans laquelle je voudrais écrire un identifiant unique généré.

Pour automatiser le traitement j'utilise une procédure appelée par la macro principale, son but est de sélectionner le range, de chercher avec un vlookup si l'identifiant unique est situé dans la plage, si négatif, j'aimerais ajouter cet identifiant à la première cellule vide de cette même colonne (ou plage). Une fois ceci fait, je clos la procédure et repasse dans ma macro principale pour générer un autre identifiant (qui sera unique ou pas) et rebelote je réappelle ma procédure pour tester.

J'ai trouvé beaucoup d'exemples avec des select et copy/paste, mais je souhaite écrire mon numéro unique qui est une variable string dans la cellule et c'est la ou cela coince.

En fait j'ai l'impression que le problème, c'est que j'écris systématiquement dans la cellule E3, je ne descend jamais à la suivante vide.

Voiçi le code que j'utilise :

Sub numero_unique_formation(numero_A_tester As String)

Dim feuille_Option As Worksheet
Dim colonne_Num_unique As Range

'Numéro unique de session de formation construite sous cette forme :
'année_mois_jour-T (ou H pour théorique/Handson)-Numéro_de_groupe (G1 /G2 / GA)-Numéro_module_formation
'Sera écrit dans le sheet4 (calcul don t touch)

'MsgBox (numero_A_tester & vbCrLf & "-" & Mid(numero_A_tester, 17, 1) & "-")

'Premieère tape consiste à controler la validité du numéro unique !
If Mid(numero_A_tester, 17, 1) = "" Then
    'MsgBox (numero_A_tester & vbCrLf & Mid(numero_A_tester, 17, 1))
    Exit Sub
End If

Set feuille_Option = Sheet4
Set colonne_Num_unique = feuille_Option.Range("E3:E3000")

'VLOOKUP(Valeur_Cherchée ; Plage_Données ; Index_Colonne ; [Valeur_Proche])
'Les paramètres utilisables sont les suivants :
'- valeur_Cherchée : valeur qui sera cherchée dans la première colonne de la plage Plage_Données ;
'- plage_Données : Plage source de la recherche ;
'- index_Colonne : Index numérique de la colonne qui contient la valeur qui sera renvoyée ;
'- [Valeur_Proche] : Précise si Excel peut utiliser la valeur inférieure la plus proche (paramètre facultatif).

'Si je n'ai pas d erreur cela veut dire que le chiffre est déjà dans la liste !

'On Error Resume Next
'MsgBox (Application.VLookup(numero_A_tester, colonne_Num_unique, 1, False))

On Error Resume Next
If Application.VLookup(numero_A_tester, colonne_Num_unique, 1, False) = xlErrNA Then

   Num_unique_bool = True
   colonne_Num_unique.End(xlUp).Offset(1).Value = numero_A_tester

Else

    Num_unique_bool = False

End If

Set colonne_Num_unique = Nothing
Set feuille_Option = Nothing

End Sub

Num_unique_bool est une variable globale de type boolean

Merci pour l'aide

David

Le 12/09/2018 à 11:56

Bonjour Dav99 le forum

un petit fichier les explications dedans et on va faire

Tes variables je pense qu'elles ne sont pas assez longue "numéro_unique_pour_vérifier_si_elle_existent" serait beaucoup mieux pour être sur de se tromper un coup sur deux !!!!

a+

papou

Le 12/09/2018 à 11:59

Bonjour David, le forum,

Difficile de t'aider à partir d'un code incomplet et détaché de son fichier. Peux-tu joindre le code dans lequel tu appelles cette procédure ? Voire idéalement un fichier avec tout ça dedans, que l'on puisse tester en direct des solutions...

Le 12/09/2018 à 12:03

Salut dav999

Bon alors perso, je trouve cette procédure bien compliqué pour l'insertion d'une valeur unique :p

Mais concentrons nous sur le problème pour lequel tu viens vers nous !

En fait j'ai l'impression que le problème, c'est que j'écris systématiquement dans la cellule E3, je ne descend jamais à la suivante vide.

Si c'est sa ton problème, alors la source provient de ta variable colonne_Num_unique et plus particulièrement :

colonne_Num_unique.End(xlUp).Offset(1).Value = numero_A_tester

Que je te conseil de remplacer par :

colonne_Num_unique.End(xlUp).Offset(1).Value + 1= numero_A_tester

Je peux pas le tester de mon côté sans ton fichier alors n'hésite pas à revenir si sa n'a pas solutionner ton problème

Le 12/09/2018 à 12:24

Re Dav999 le forum

Num_unique_bool = True
   colonne_Num_unique.End(xlUp).Offset(1).Value = numero_A_tester

tu remplaces par:

Num_unique_bool = True
fin=feuille_Option.Range("E" & rows.count).end(3).row+1
feuille_Option.Range("E" & fin)= numero_A_tester

mais de toute façon ton code est à revoir !!!!

a+

papou

Le 12/09/2018 à 16:46

Merci à tous,

Oui j'ai repéré quelques coquilles, en fait ca fonctionne quand je change :

Set colonne_Num_unique = feuille_Option.Range("E3:E3000")

par ca :

Set colonne_Num_unique = feuille_Option.Range("E3000")

Je vais quand même regarder vos astuces car j'ai toujours quelque chose qui me contrarie

Merci à tous pour vos réponses

Le 12/09/2018 à 16:55

Re Dv999 le forum

en fait ton code il est bon a être jeté et en refaire un de 10 lignes qui lui fera ce que tu veux!!!

de plus tes variables avec des noms à rallonges c'est une bêtise

mais comme tu ne veux pas passer ton fichier bah !!!! Continue donc tout seul.

a+

Papou

Le 13/09/2018 à 08:54

Merci Papou,

en ayant obtenu une réponse sur un autre post, je comprends maintenant pourquoi les erreurs ne sortaient pas, à cause de ma mauvaise gestion des erreurs sous excel. Du coup cela ne me permettait pas de trouver d'où venait le problème.

Je vais essayer de m'en sortir seul, je réactive le post si je n'y arrive pas.

De plus j'ai lu que VLOOKUP ne fonctionne que sur des plages d'au moins 2 colonnes, or avec ma colonne unique je déclenchais une erreur qui ne sortait pas à cause de ma gestion désastreuse des erreurs.

Merci pour ta réponse, je vais effectivement revoir tout le code avec ce que je sais maintenant.

Bonne journée

Le 13/09/2018 à 09:01
dav999 a écrit :

Merci Papou,

en ayant obtenu une réponse sur un autre post, je comprends maintenant pourquoi les erreurs ne sortaient pas, à cause de ma mauvaise gestion des erreurs sous excel. Du coup cela ne me permettait pas de trouver d'où venait le problème.

Je vais essayer de m'en sortir seul, je réactive le post si je n'y arrive pas.

De plus j'ai lu que VLOOKUP ne fonctionne que sur des plages d'au moins 2 colonnes, or avec ma colonne unique je déclenchais une erreur qui ne sortait pas à cause de ma gestion désastreuse des erreurs.

Merci pour ta réponse, je vais effectivement revoir tout le code avec ce que je sais maintenant.

Bonne journée

La fonction TaPlage.Find(Quoi?,lookin:=xlValues,lookat:=xlWhole) serait plus adaptée.

Le 13/09/2018 à 09:13

Voilà un code que tu peux utiliser pour générer automatiquement un Identifiant unique qui viendra se placer dans ta colonne E :

Si tu veux t'en inspirer ou l'utiliser ^^

Sub ID_unique()
' Déclaration variable
Dim Lig As Integer, ID_num As Integer, x As Integer
Dim ID_lettre1 As String, ID_lettre2 As String, ID_lettre3 As String
' Compte le nombre d'indice présent dans ma colonne E
Lig = Range("E" & Rows.Count).End(xlUp).Row + 1
' Genère un chiffre entre 1 et 999
Randomize
ID_num = Int(999 * Rnd) + 1
' Génère trois lettres
Randomize
ID_lettre1 = Int((26 * Rnd) + 1)
ID_lettre2 = Int((26 * Rnd) + 1)
ID_lettre3 = Int((26 * Rnd) + 1)
' On regroupe ce qui a été généré dans la même variable
ID_lettre1 = Chr(ID_lettre1 + 64) & Chr(ID_lettre2 + 64) & Chr(ID_lettre3 + 64) & ID_num
' On boucle pour chercher si une valeur identique est existante dans ma colonne E
For x = 1 To Lig
    If Cells(x, 5) = ID_lettre1 Then    'Et si c'est le cas
        Call ID_unique                  'On rappel notre macro
    End If
Next
' Si la valeur est bien unique, alors on la place en fin de colonne E
Cells(Lig, 5) = ID_lettre1

End Sub
Le 13/09/2018 à 09:17

Bonjour Juice !

Ne serait-il pas plus simple de générer un numéro unique non pas de façon aléatoire mais suivant une suite logique ? Par exemple, un nombre égal au maximum déjà présent auquel on ajoute tout simplement 1 ?

Le 13/09/2018 à 09:20
Pedro22 a écrit :

La fonction TaPlage.Find(Quoi?,lookin:=xlValues,lookat:=xlWhole) serait plus adaptée.

Ok merci pour l'info je vais regarder à quoi ca ressemble

@Juice : Je génère moi même le nombre unique, cette partie ne posait pas de problème et n'en pose toujours pas, mais merci je garde cela sous le coude ca pourra servir à un autre moment.

Voila à quoi ca ressemble maintenant et ca fonctionne bien :

Sub numero_unique_formation(numero_A_tester As String)

Dim feuille_Option As Worksheet
Dim colonne_Num_unique As Range

Set feuille_Option = Sheet4
Set colonne_Num_unique = feuille_Option.Range("E2000")

'Premieère tape consiste à controler la validité du numéro unique !
If Len(numero_A_tester) < 17 Then
    Exit Sub
End If

'Si je n'ai pas d erreur cela veut dire que le chiffre est déjà dans la liste !

'Si le retour de la fonction ISNA est true, cela veut dire que vlookup ne trouve pas le numéro de training, dans ke
If WorksheetFunction.IsNA(Application.VLookup(numero_A_tester, Sheet4.Range("E3:F2000"), 1, False)) = True Then
   Num_unique_bool = True
   colonne_Num_unique.End(xlUp).Offset(1).Value = numero_A_tester
Else
    Num_unique_bool = False
End If

End Sub
Le 13/09/2018 à 09:24

Coucou Pedro,

Le truc c'est qu'on ne connais pas la mise en forme des ID de dev999 :p

Personnellement, tout comme tu le souligne, pour mon propre fichier je fais une suite logique de chiffre en me servant simplement du numéro de ligne.

Mais bon, je me suis dit que j'allais lui fournir un code pour réellement générer un ID aléatoire et unique x)

Le 13/09/2018 à 09:29
dav999 a écrit :
Pedro22 a écrit :

La fonction TaPlage.Find(Quoi?,lookin:=xlValues,lookat:=xlWhole) serait plus adaptée.

Ok merci pour l'info je vais regarder à quoi ca ressemble

@Juice : Je génère moi même le nombre unique, cette partie ne posait pas de problème et n'en pose toujours pas, mais merci je garde cela sous le coude ca pourra servir à un autre moment.

Voila à quoi ca ressemble maintenant et ca fonctionne bien :

Sub numero_unique_formation(numero_A_tester As String)

Dim feuille_Option As Worksheet
Dim colonne_Num_unique As Range

Set feuille_Option = Sheet4
Set colonne_Num_unique = feuille_Option.Range("E2000")

'Premieère tape consiste à controler la validité du numéro unique !
If Len(numero_A_tester) < 17 Then
    Exit Sub
End If

'Si je n'ai pas d erreur cela veut dire que le chiffre est déjà dans la liste !

'Si le retour de la fonction ISNA est true, cela veut dire que vlookup ne trouve pas le numéro de training, dans ke
If WorksheetFunction.IsNA(Application.VLookup(numero_A_tester, Sheet4.Range("E3:F2000"), 1, False)) = True Then
   Num_unique_bool = True
   colonne_Num_unique.End(xlUp).Offset(1).Value = numero_A_tester
Else
    Num_unique_bool = False
End If

End Sub

Ton code te sert juste à dire si un numéro unique spécifié est retrouvé dans ta plage, n'est ce pas ?

Dans ce cas il s'agirait plutôt d'écrire une fonction avec en entrées : le N° unique, la plage où le chercher, et qui te donne une valeur booléenne (oui je le trouve, non je ne le trouve pas). Au final, c'est un besoin courant et c'est donc déjà existant dans Excel ou dans VBA.

D'après moi, tout ton code peut être remplacé par :

Resultat=Sheet4.Range("E3:F2000").Find(numero_a_tester, lookin:=xlValues, lookat:=xlWhole)

S'il n'est pas retrouvé : resultat = nothing, sinon résultat est la cellule contenant ton numéro unique.

Le 13/09/2018 à 10:42

Oui c'est presque cela, sauf que lorsque je construit le numéro unique ,il est composé de plusieurs strings, et une en particulier peut être absente, du coup je dois tester la longueur de chaine avant de faire le vlookup ou d'utiliser ta fonction.

Il semblerait donc que je ne puisse pas le résumer à cette seule ligne de code

Le 13/09/2018 à 10:48

Mais si ton numéro unique est déconnant, tu le retrouveras de toute façon pas dans ta liste, non ?

Sinon tu peux ajouter un contrôle :

If Len(numero_a_tester) > 17 Then Resultat=Sheet4.Range("E3:F2000").Find(numero_a_tester, lookin:=xlValues, lookat:=xlWhole)
Rechercher des sujets similaires à "ecrire premiere vide colonne"