Appel aux intellectuels, ma fonction donne #Valeur

Bonjour,

Pourquoi la fonction marche quand appelée dans un sub mais pas dans le worksheet ?

Supposons dans une feuille :

A1 = Toto

A2 = Toto

A3 = Toto

A4 = Toto

B1 = 1

B2 = 2

B3 = 3

B4 = 4

Et n'importe ou

=recherchevaleatoire(A1;A1:B4;1)

J'ai l'erreur #Valeur... Mais je ne comprends pas pourquoi...

Function recherchevaleatoire(r1 As Range, r2 As Range, i As Integer) As Variant
Dim tablo() As Variant, aleat As Integer
Application.Volatile True
Set x = r2.Find(r1.Value)
If Not x Is Nothing Then
    firstAddress = x.Address
    Do
        ReDim Preserve tablo(j)
        tablo(j) = x.Offset(0, i)
        Set x = r2.FindNext(x)
        j = j + 1
    Loop While Not x Is Nothing And x.Address <> firstAddress
End If
aleat = Application.WorksheetFunction.RandBetween(LBound(tablo), UBound(tablo))
recherchevaleatoire = tablo(aleat)
End Function

Sub toto()
y = recherchevaleatoire(Sheets("Feuil1").Range("A1"), Sheets("Feuil1").Range("A1:B4"), 1)
MsgBox (y)
End Sub

Merci

E

bonjour

rechervaleatoire : es-tu certain que ça existe ?

une idée ici

https://forum.excel-pratique.com/excel/recherche-aleatoire-t4471.html

bonjour

RandBetween(LBound(tablo

c'est quoi le L ?

cordialement

Ca n'existe pas c'est pourquoi j'essaie de la construire à la mano!

Lbound = Lowerbound = Borne basse

Dim tablo(1 to 30)

l = Lbound(tablo)

u = ubound(tablo)

l = 1 u = 30

Le problème est que la fonction me donne #valeur alors qu'elle marche dans mon sub... Pourquoi ?

Bonjour @ tous,

Il faut mettre les valeurs numériques en colonne A et les valeurs Textes en colonne B.

@ + +

Alors je réexplique mon problème. Ma fonction marche très bien quand elle est appellée par une routine

Sub toto()
y = recherchevaleatoire(Sheets("Feuil1").Range("A1"), Sheets("Feuil1").Range("A1:B4"), 1)
MsgBox (y)
End Sub

Par contre quand je l'appelle depuis une feuille :

=recherchevaleatoire(A1;A1:B4;1)

J'ai #valeur

Je souhaiterai qu'elle marche aussi sur la feuille.

Quelqu'un saurait il trouver le problème ?

Bien à vous,

E

Bonjour,

je suppose que tu es arrivé au même constat que moi,

appel depuis vba, le findnext fonctionne bien

appel depuis feuille excel, le findnext semble perdre les pédales et renvoie nothing puis plante la macro sans message d'erreur sur le do while

Par contre, je ne suis pas (encore ?) parvenu à trouver pourquoi ...

Tout à fait, même constat. Je n'ai donc pas une poutre dans l’œil et c'est bien le findnext qui renvoie nothing sans raison apparente.

Bonjour,

Un problème similaire avec FindNext a déjà été évoqué

https://forum.excel-pratique.com/excel/find-findnext-probleme-de-bouclage-t40797-10.html

Tu peux tenter

Function recherchevaleatoire(r1 As Range, r2 As Range, i As Integer) As Variant
Dim tablo() As Variant, aleat As Integer
Dim Position As Range
Dim x, firstAddress, j, n
    Application.Volatile
    Set Position = r1
    firstAddress = Position.Address
    Do
        Set x = r2.Find(r1.Value, Position)
        If Not x Is Nothing Then
            ReDim Preserve tablo(j)
            tablo(j) = x.Offset(0, i)
            j = j + 1
            Set Position = x
        End If
    Loop While x.Address <> firstAddress
    aleat = Application.WorksheetFunction.RandBetween(LBound(tablo), UBound(tablo))
    recherchevaleatoire = tablo(aleat)
End Function

A+

Merci à tous, en effet, il faut finalement contourner le problème du findnext en bouclant sur des .find en excluant les valeurs précédentes.