CODEWITHSUNDEEP
excelvbafor-loopevaluate
nicktrent
nicktrent

Reputation: 49

Excel VBA Type mismatch error with Evaluate

I have a For loop to examine whether there are any remainder when I divide the row numbers of column A by 10. For this purpose, I use the MOD function and define a variable by the applying Evaluate. If the result of this variable zero, the code should do additional tasks which are OK. My problem is when the routine enters the IF statement, I receive a Run time error 13: Type mismatch. Could you please help me why? Here is my code:

Sub copcol()

Dim nmr As Variant

Sheets(1).Activate

lrow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To lrow

nmr = Evaluate("=MOD(ROW(A" & i & "),10)")

If nmr = 0 Then
'Do something

Upvotes: 1

Views: 69

Answers (1)

FunThomas
FunThomas

Reputation: 29511

(1) The reason for the type mismatch is a little bit surprising: The formula will return an array with one element, and you can't check the value of a whole array. Two ways to fix that:

  • make a single value formula by putting a @ in front of the formula:
    Evaluate("=@MOD(ROW(A" & i & "),10)")
  • Leave the formula as it is but use an index into the result:
    If nmr(1) = 0 Then

(2) Both variants are much too complicated. There is no reason to use the Row-function when we have the number already in i. The following statement is already a little bit easier:

nmr = Evaluate("=@MOD(" & i & ",10)")

(3) There is no reason to use the Evaluate-function to call the Excel version of MOD. Use the native VBA mod function Mod instead:

nmr = i Mod 10

There is hardly any reason to use Evaluate in VBA except maybe if you need complex mathematic formulas. Personally, I cannot remember using Evaluate more that maybe a handful times in my whole VBA career. Evaluate makes the code much slower and opens the door to hard to find errors.

(4) If you really want to do something only on every 10th row, you can specify this already in your For-loop (Scott Craner suggested this in the comments:)

For i = 10 to lrow step 10
   (...)
Next i

Upvotes: 6

Related Questions