Trying to continue macro when value is #N/A

Hi,

I'm trying to solve the following problem. In Excel I need to lookup some value's for which I use the VLOOKUP function. Sometimes I've got a result, but sometimes it's unavailable since it doesn't exist or anything like that, which is fine. However I need to run a loop in which I have to check all the values and when there is a match, it should do something with it. When there is nothing available, it should continue to check. This part I can't get to work.

I've tried

Please Login or Register  to view this content.

and tried

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

But they don't work. I would be very happy to find a way to fix this. The full code is as following:

Please Login or Register  to view this content.

PS, the 1 to 18 will change to around 1500 in the future, this is just a testcase.

How about just adding

Please Login or Register  to view this content.

at start of the code?

How about just:

Please Login or Register  to view this content.

I've now tried the GoTo code

Please Login or Register  to view this content.

Still get stuck at "If Range("D46").Value <> "0" Then"

Is the ErrHandler not valid?

Tried the following:

Please Login or Register  to view this content.

But it says Compile error, Next without for

Originally Posted by larstk

Is the ErrHandler not valid?

No - once you've called an error handler, via On Error, error handling is suspended until you either exit the subroutine you're in or use a resume statement. So your error handler will work the first time you call it, but not the 2nd (because, as far as VBA is concerned you're still handling the first error).

It would work if you changed the line to:

Please Login or Register  to view this content.

But using the IsError function is much neater way of dealing with the problem.

This is the structure you need:

Please Login or Register  to view this content.

Thanks! But now it stops instead of moving on.

Tried this but wouldn't work either.. :

Please Login or Register  to view this content.

yeah but it stops when an error occurs instead of trying the next one

Originally Posted by Norie

You don't need this.

Please Login or Register  to view this content.

Which next one? There's nothing in your code to tell it to look at a cell other than D46. Are you trying to get it to loop through a range of cells?

Yes but I'm changing one cell (D3) and thereby this changes cell D46 (and some other cells). The whole lot is copied to another sheet and then 1 is added to cell D3 until I've reached all of them.

This is now the code:

Please Login or Register  to view this content.

Yes sorry!,

Vlookup is in a cell

Originally Posted by Norie

What exactly are you trying to do?

In your first post you mention VLOOKUP but there's nothing in the code for a VLOOKUP.

Is the VLOOKUP in a cell?

Okay had to change a couple of things to make things private.

The invoice is the one wich its all about.

So you'll have about 1,500 invoices stored in a rough database format, and you want to loop through them, 1 by 1, and put each of them on its own formatted sheet, within the same workbook?

Is that roughly what you're after?

Originally Posted by Andrew-R

So you'll have about 1,500 invoices stored in a rough database format, and you want to loop through them, 1 by 1, and put each of them on its own formatted sheet, within the same workbook?

Is that roughly what you're after?

Yes or make a new workbook for each of them. Thing is, most of them will be empty and I don't want them to be copied. Some will "not exist" so those need to be skipped. It's a database with unique members and they all have an unique number.

Originally Posted by larstk

Yes or make a new workbook for each of them.

I've got to ask ... why? You've got them all in a database format, so what's the benefit of saving each of the individually?

Originally Posted by Norie

I'm not 100% sure what you are doing but I've got some idea.

Anywy, try this.

Please Login or Register  to view this content.

Great!! Apart from the wrong copying of the "subtotal invoice" it works exactly as I would like to many thanks!

Originally Posted by Andrew-R

I've got to ask ... why? You've got them all in a database format, so what's the benefit of saving each of the individually?

True but I need to sent them out through email. Need to work on that as well. I know there is something like mailmerge but it doesn't work exactly as I want.

Pasting them all on to separate sheets doesn't make them any easier to send through e-mail. I'd really have a careful think about this, if I were you

Originally Posted by Norie

I noticed that, shouldn't be too hard to fix - perhaps use paste value for the formulas.

By the way, why don't you just loop through the rows on the 'Wedstrijdstarts compo'?

Then you wouldn't have any #N/A problems.

Not sure what you mean.. You mean that you would do a loop in the sheet?

Originally Posted by Andrew-R

Pasting them all on to separate sheets doesn't make them any easier to send through e-mail. I'd really have a careful think about this, if I were you

If you have a suggestion I'm eager to hear. I'm using Gmail and not using any "mail software".

You mean, run the A1 column ("Debiteurnr#") of the "Wedstrijdstarts compo" sheet on the "Invoice" sheet directly? I would like to do that because that would save time and useless processing. But which function do I need for that since VLOOKUP is not going to work for this. And the sheets need to keep their names corresponding to that "Debiteurnr#" which they do now.

Solved, I think by changing

Please Login or Register  to view this content.

Into

Please Login or Register  to view this content.

Ah yeah that's a very neat solution! Works even better since it's now adapting to the the range which can change of course. That's very helpful!