Can't Find Value

[majikman]

i'm trying to search in a hidden worksheet that i've created for use
names. below is the code but it doesn't work. can someone please hel
me out? btw, i'm really curious as to where i can find more informatio
about that find method and for that matter, all the method's that ar
used in excel. the excel help file is very lacking in information. i
doesn't list nearly as many parameters for the find function as i'v
seen in examples here in the forums.

Public Function VerifyPW(Name As String) As Boolean
Dim Found As WorksheetFunction
With Worksheets(5).Range("A1:A30")
Set Found = .Find(What:=Name, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With

VerifyPW = Not (Found Is Nothing)
VerifyPW = True
End Functio

 

[Harald Staff]

Hi

You've declared
Dim Found As WorksheetFunction
but your code should return a range:
Dim Found As Range

(If you used Option Explicit on top of your modules then you'd receive a
helpful "Type mismatch" error.)

HTH. Best wishes Harald

 

[majikman]

thanks for the help harald. However, it still doesn't work. I set
watch on Found and it keeps showing that its Nothing. btw, i did us
option explicit at the very top of my module so i'm not sure why i
didn't return me a type mismatch that time because it has in the past

 

[Guest]

Found will = Nothing if no match is made. Do a test with a known value firs

----- majikman > wrote: ----

thanks for the help harald. However, it still doesn't work. I set
watch on Found and it keeps showing that its Nothing. btw, i did us
option explicit at the very top of my module so i'm not sure why i
didn't return me a type mismatch that time because it has in the past

 

[majikman]

i am doing that. thats why i'm saying its not working because i am
testing with a known value and it continuously returns nothing

 

[majikman]

ok, so i've updated my code and now it looks like this

Public Function VerifyPW(Name As String) As Boolean
Dim rng As Range
Dim Found As Range
Set rng = Worksheets("Users").Columns("A:A")
With rng
Set Found = .Find(What:="dan", MatchCase:=True)
End With

VerifyPW = Not (Found Is Nothing)
VerifyPW = True
End Function

if i explicitly state What as "dan", it will find it properly. However
if i try to use my Name variable, it won't work anymore. when I use m
name variable, i use What:=Name. Do i need to put name inbetween som
kind of characters, like quotes, to let it know that its a variable

 

[Patrick Molloy]

Public Function VerifyPW(Name As String) As Boolean
Dim rng As Range
Dim Found As Range



Set rng = Worksheets("Users").Columns("A:A")

Set Found = rng.Find(What:="dan", MatchCase:=True)

VerifyPW = Not (Found Is Nothing)

End Function