We help IT Professionals succeed at work.

Handling Multiple Error Possibilities

John Carney
John Carney asked
on
136 Views
Last Modified: 2013-01-23
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
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
File not found error
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Just to be clear, what ssaqibh means is that you forgot to attach your workbook.
John CarneyReliability Business Tools Analyst II

Author

Commented:
That fits with the kind of day I had yesterday that I would forget to attach the workbook! :- )
XYZ-Converter-Demo.xlsm
Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, ssaqibh. Sorry for doing one of my famous disappearing acts!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.