Handling Multiple Error Possibilities

I can't seem to remember how to do this. Please take a look at the attached workbook. Column D has numbers that designate the week in which each incident occurred. (Dates in Column N). The "SpinOffWeeks' macro would work fine except that not every week is accounted for. Week 16 is followed by Week 19, Week 19 is followed by Week 21, etc.

The macro as I have it now will handle the Week 16 error, but it bugs on the Week 19 error.

How do I handle the fact that the sought-after number for 'btm' isn't always i + 1?

Set top = Incidents.Columns("D:D").Find(What:=i, After:=[D4], LookIn:=xlValues)
Set btm = Incidents.Columns("D:D").Find(What:=i + 1, After:=[D4], LookIn:=xlValues).Offset(-1, 0)

Open in new window

I need something that will find the next number higher than i, whether it's i + 1 or i + 2 or i +7, etc.

Thanks for solving my day ruiner! Here's to a better tomorrow :- )

John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try

Set btm = top.Offset(WorksheetFunction.CountIf(Range("D:D"), i) - 1)
0
 
Saqib Husain, SyedEngineerCommented:
File not found error
0
 
Martin LissOlder than dirtCommented:
Just to be clear, what ssaqibh means is that you forgot to attach your workbook.
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
That fits with the kind of day I had yesterday that I would forget to attach the workbook! :- )
XYZ-Converter-Demo.xlsm
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, ssaqibh. Sorry for doing one of my famous disappearing acts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.