Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find works sometimes
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find works sometimes
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 On 29 Aug., 20:53, Bonsai Bill wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find works sometimes
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" wrote: 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 On 29 Aug., 20:53, Bonsai Bill wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find works sometimes
Hi
Thanks for your reply. I'm glad to help. Regards, Per On 29 Aug., 22:14, Bonsai Bill wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Works spreadsheet-find circular ref. | Excel Worksheet Functions | |||
Can I create a macro that works like the "find' function | Excel Discussion (Misc queries) | |||
find works very oddly | Excel Programming | |||
Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up | Excel Programming | |||
VB code almost works...but can't find a minor mistake | Excel Programming |