Avatar of John Carney
John Carney
Flag for United States of America asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
John Carney

8/22/2022 - Mon
Saqib Husain

File not found error
Martin Liss

Just to be clear, what ssaqibh means is that you forgot to attach your workbook.
John Carney

ASKER
That fits with the kind of day I had yesterday that I would forget to attach the workbook! :- )
XYZ-Converter-Demo.xlsm
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Carney

ASKER
Thanks, ssaqibh. Sorry for doing one of my famous disappearing acts!