CODEWITHSUNDEEP
vbaexceltype-mismatch
Cindy Ni
Cindy Ni

Reputation: 3

Type mismatch error 13 in VBA

so I am writing code for a macro in VBA, and whenever I try to run it, I always get the same error on one line. What is strange about this is that the line works earlier in the program, but it stops working the second time I use it (I did not change any code; I literally copied and pasted it). This is the code: 

positioninDailyVisitors = Application.Match(originaldata.Worksheets("Top Visitors Chart").Range(columns(index) & counter + 11), originaldata.Worksheets("Daily Visitors").Range("A5:A627"), 0)

A bit of background: positioninDailyVisitors is an integer variable, columns(index) is an array of type String that lists values like "A", "B", etc. Also, I already tried to use Application.WorksheetFunction.Match and it still does not work.

Upvotes: 0

Views: 1092

Answers (1)

Vegard
Vegard

Reputation: 4882

If Application.Match doesn't find anything, it returns a Variant/Error to the caller - and you're trying to assign that error to an integer-only variable ==> type mismatch. Betting good money this is your problem since the code works occasionally.

Excuse my sub-par hand-drawing skills:enter image description here

Modify your code thusly:

If Not IsError(Application.Match(originaldata.Worksheets("Top Visitors Chart").Range(columns(index) & counter + 11), originaldata.Worksheets("Daily Visitors").Range("A5:A627"), 0)) Then & _
   positioninDailyVisitors = Application.Match(originaldata.Worksheets("Top Visitors Chart").Range(columns(index) & counter + 11), originaldata.Worksheets("Daily Visitors").Range("A5:A627"), 0)

Or perhaps more workable/readable:

Dim errVar As Variant
errVar = Application.Match(originaldata.Worksheets("Top Visitors Chart").Range(columns(index) & counter + 11), originaldata.Worksheets("Daily Visitors").Range("A5:A627"), 0)
If Not IsError(errVar) Then positioninDailyVisitors = CInt(errVar)

Upvotes: 2

Related Questions