MATCH mess

[tradinup2]

Hi all,

I have 12 worksheets to cycle thru to match a value (recID) in column A and then clear the contents in that row and write new info to.

if I set n=1 and the col has a match, it returns the correct row number, but if there is no match I get the 1004 error.
When I tried to handle the error I made a mess.
Now I get next without for.

I might punching above my weight on this effort.

Please Login or Register  to view this content.

thanks

[mat.davies]

What's the 'Next n' doing below 'If myvalue is nothing then'?, if you're trying to go to the next item then I'd just change the if statement to 'If not (myvalue is nothing) then' and put the code to execute before the end if (i.e. skip straight to the 'next n') otherwise, if you're trying to break the loop, you neec 'exit for'.

It's a bit messy but you could try error trapping, the following code should/might work

Please Login or Register  to view this content.

Cheers

Mat

[tradinup2]

I appreciate the response, but with your code I still get an error 1004 when there is no match.
The only reason for the IsNothig was to handle the error, which it did, but I can't figure out how to advance to the next n.

[mat.davies]

Sorry, it was erroring on the 2nd loop. Hadn't tested it thouroughly.

Try this

Please Login or Register  to view this content.

I can explain it if it works!

Cheers

Mat

[mat.davies]

Sorry, it was erroring on the 2nd loop. Hadn't tested it thouroughly.

Try this

Please Login or Register  to view this content.

I can explain it if it works!

Cheers

Mat

[tradinup2]

Thanks Mat
It appears to do what need.
A line by explanation would be good. I'm glad to have the code, but it's better to understand it so maybe I don't have to ask so many questions and I can use
it somewhere else.

[mat.davies]

No problem, wanted to check it worked before I launched into an explanation!

There's not really a lot different from your previous code. just these three lines really

Please Login or Register  to view this content.

just keeps ploughing on if it sees an error. 'On error goto N' (in my 1st post) will jump to line N on the 1st error, but raise an exception on a 2nd - which is no good if your using it (there might be something to clear the error status and resume, but i can't remember what it is)

Please Login or Register  to view this content.

ensures myvalue is cleared from one loop to the next

Please Login or Register  to view this content.

Just checks that the value in myvalue has changed (i.e. that it is no longer = nothing) and runs code if it has.

good luck

Mat

[tradinup2]

Two more questions.
On Error GoTo 0 'switch off error trap

how does this turn off error trapping

and

10 Next n

why the line numbers

thanks

[mat.davies]

Hi

On error goto 0 reverts to the VBA default error handler, it should trigger the message box were all way to familar with

10 next n is just the close of the for loop on 'n' (you can just use next, but its a bit cleaner to say which control variable you're indexing), in this case it has been labelled as line 10 so the earlier "error goto 10" will send things there. This solution is still a bit messy, but I think a more robust one would likely be very long, and might not be of any use.

Cheers

Mat

Cheers

Mat