MATCH mess

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.

Sub test_match(rec) ', wsName As String)
Dim myvalue As Variant
'Stop
'n = 2
'wsName = "master"
For n = 1 To 12
    'Set ws1 = Sheets(n)
    Sheets(n).Activate
    myvalue = WorksheetFunction.Match(rec, Range("A1:A150"), 0)
        If myvalue Is Nothing Then
        Next n
        End If
MsgBox (myvalue)
Next n
End Sub

thanks

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

For n = 1 To 12
    'Set ws1 = Sheets(n)
    Sheets(n).Activate
    On Error GoTo 10 'switch on error trap (catches #N/A errors)
    myvalue = WorksheetFunction.Match(rec, Range("A1:A150"), 0)
    On Error GoTo 0 'switch off error trap
    MsgBox (myvalue)
10 Next n

Cheers

Mat

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.

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

Try this

For n = 1 To 12
    On Error GoTo 0 'switch off error trap
    'Set ws1 = Sheets(n)
    Sheets(n).Activate
    On Error Resume Next
    Set myvalue = Nothing
    myvalue = WorksheetFunction.Match(rec, Range("A1:A150"), 0)
    If Not myvalue Is Nothing Then
        MsgBox (myvalue)
    End If
10 Next n

I can explain it if it works!

Cheers

Mat

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

Try this

For n = 1 To 12
    On Error GoTo 0 'switch off error trap
    'Set ws1 = Sheets(n)
    Sheets(n).Activate
    On Error Resume Next
    Set myvalue = Nothing
    myvalue = WorksheetFunction.Match(rec, Range("A1:A150"), 0)
    If Not myvalue Is Nothing Then
        MsgBox (myvalue)
    End If
10 Next n

I can explain it if it works!

Cheers

Mat

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.

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

On Error Resume Next

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)

Set myvalue = Nothing

ensures myvalue is cleared from one loop to the next

If Not myvalue Is Nothing Then

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

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

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