Find works sometimes

[Bonsai Bill]

I have a string array containing numbers like 0704. Put in string to keep
leading zero. I have a sheet that contains some of these array values in
Row(1). I created a byte array to tell program if column heading exists. I
used Find yesterday and it populated array properly. Today it doesn't. Here
is the code:

On Error Resume Next
For I = 1 To NumBltExams
Sheets(FIASht).Rows(1).Find(BltExamDateArray(I), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If Err.Number = 0 Then ' have FIA data
FIAExamArray(I) = 1
Else
FIAExamArray(I) = 0
Err.Clear
End If
Next I
On Error GoTo 0

Original code had no parameters set so I assume that I am missing a
parameter setting. Parameters above were picked up from running macro
recorder.

Code was written in ExcelXP and Vista. I ran macro in Office2007 first
today. It failed first time but ran second time. Then tried it in ExcelXP and
can not get it to work. I don't think this had any effect but am including
anyway.

Finally err.Number = 91 if string is missing but 1004 if present. I could
use this to set values above but would rather have err.Number of 0 when found.

Any suggestions would be greatly appreciated!

 

[Per Jessen]

Hi

Try if this solve your problem:


For I = 1 To NumBltExams
Set found = Sheets(FIASht).Rows(1).Find(What:=BltExamDateArray(I),
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not found Is Nothing Then ' have FIA data
FIAExamArray(I) = 1
Else
FIAExamArray(I) = 0
End If
Next

Regards,
Per

 

[Bonsai Bill]

Hi Per,

Your suggested code worked fine. I have another "find" that stopped working
and will revised it with your code.

Thanks for your quick and accurate solution!

 

[Per Jessen]

Hi

Thanks for your reply.

I'm glad to help.

Regards,
Per