Thinking out loud: Can you help me with this bug?

[cheoksoon]

Hi everyone,

I'm in a bit of a pickle...

I've got a VBA script that was working just fine... until I added the following lines:

On Error GoTo here

find_row = Workbooks("data.xlsm").Application.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1,2), Worksheets("Data5").Range("A:A"),0)
Workbooks("data.xlsm").Sheets("Data5").Cells(find_row,3) = "New data" 

here:

'Code continues on... bla bla bla'

data.xlsm is a publicly shared file. It is not a read-only file.

I'm getting a random error that says:

"Runtime error 13: Type mismatch

with the second line.

And this is despite me using the error handler!

This error doesn't occur most of the time the script is run, only may 1 out of 10 times.
And this error occurs sometimes on my computer, sometimes on another computer, but never the same computer.
Removing these two lines solves the problem.
And everything was perfectly fine before I added these two lines.

I couldn't replicate the error because it comes on randomly.

Can somebody tell me what could be the problem here??

I'm thinking:

1. Is it something to do with the code? I have debugged every component of those 2 lines and the error doesn't change.
2. Is it something to do with the clashing versions of excel between different computers?
3. Is it something to do with individual computer quirks?
4. Is it something to do with just excel being buggy?
5. Is it something to do different versions of Windows excel was installed on?
6. What could it be?

 

[wideboydixon]

The problem is that Application.Match() doesn't throw an error; it will give you an error as a return value. Try this instead:

find_row = Application.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1, 2).Value, Worksheets("Data5").Range("A:A"), 0)
If Not IsError(find_row) Then Workbooks("data.xlsm").Sheets("Data5").Cells(find_row, 3) = "New data"

Alternatively, I believe WorksheetFunction.Match() will throw an error:

On Error GoTo here

find_row = Application.WorksheetFunction.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1,2), Worksheets("Data5").Range("A:A"),0)
Workbooks("data.xlsm").Sheets("Data5").Cells(find_row,3) = "New data" 

here:

'Code continues on... bla bla bla'

WBD

 

[Norie]

Have you checked what find_row is?

If it's an error value, i.e. Application.Match failed, that could explain the error message.

PS You don't actually need Workbooks("data.xlsm").Application.Match, you could just use Application.Match.

 

[cheoksoon]

The problem is that Application.Match() doesn't throw an error; it will give you an error as a return value. Try this instead:

find_row = Application.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1, 2).Value, Worksheets("Data5").Range("A:A"), 0)
If Not IsError(find_row) Then Workbooks("data.xlsm").Sheets("Data5").Cells(find_row, 3) = "New data"

Alternatively, I believe WorksheetFunction.Match() will throw an error:

On Error GoTo here

find_row = Application.WorksheetFunction.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1,2), Worksheets("Data5").Range("A:A"),0)
Workbooks("data.xlsm").Sheets("Data5").Cells(find_row,3) = "New data" 

here:

'Code continues on... bla bla bla'

WBD

Thanks WBD,

But can you explain why this error comes on so randomly? Like it only happens 1 out of 10 times (or less) I run the script. Everything else being the same, I can't understand why excel is doing this.

I will implement your idea and let's hope it's gonna be ok.

 

[cheoksoon]

Have you checked what find_row is?

If it's an error value, i.e. Application.Match failed, that could explain the error message.

PS You don't actually need Workbooks("data.xlsm").Application.Match, you could just use Application.Match.

Hi Norie,

Yeap, I checked it again and again. The code works 9 out of 10 times. For some strange reason, it returns "type mismatch" 1 out of 10 times I run the script. Everything else being the same, I couldn't figure out why this is happening.

I tried debugging using:

 MsgBox find_row

it still spit out "type mismatch". Yet other times it works by spitting out an integer (which is the row number I'm looking for).

 

[dmt32]

Hi,
rather than testing the whole column, try sizing the range you are matching your values against.

Untested but see if following helps

   Dim arr As Variant, find_row As Variant
    With Workbooks("data.xlsm").Worksheets("Data5")
        arr = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value2
        find_row = Application.Match(.Cells(1, 2), arr, 0)
        If Not IsError(find_row) Then .Cells(CLng(find_row), 3) = "New data"
    End With

You can catch the return error (no match) by using the IsError function but this will require your variable find_row to be declared as variant which you coerce to long data type for match results.

Hope Helpful

Dave

 

[cheoksoon]

Oh one more thing I forgot to mention...

"data.xlsm" is actually an external file I opened with VBA to extract some data from. I think that's why I'm using application.Match instead of Worksheetfunction.Match. Worksheetfunction doesn't work with this.

 

[wideboydixon]

Thanks WBD,

But can you explain why this error comes on so randomly? Like it only happens 1 out of 10 times (or less) I run the script. Everything else being the same, I can't understand why excel is doing this.

I will implement your idea and let's hope it's gonna be ok.

You'll only get the error when there's no match.

WBD

 

[Norie]

Instead of using On Error.. to handle the possible error use IsError.

find_row = Workbooks("data.xlsm").Application.Match(Workbooks("data.xlsm").Sheets("Data").Cells(1,2), Worksheets("Data5").Range("A:A"),0)

If Not IsError(find_row) Then
    Workbooks("data.xlsm").Sheets("Data5").Cells(find_row,3) = "New data" 
Else
    MsgBox "Not found!
End If