CODEWITHSUNDEEP
vbaexcel
Obisam
Obisam

Reputation: 5

VBA Array Error 2007 if statement

Probably its a simple and common question but I did not find anything in google. I store some data in array but sometimes this data has error value, in this case xlErrDiv0 2007 #DIV/0!

I use this array in a loop so i need to check if I loop through this incorrect value.

I tried: 

If vRangeShift(1, i) <> CVErr(xlErrDiv0) Then
End if

If vRangeShift(1, i) = "Error 2007" Then

and some others options but I always receive type mismatch error. vRangeShift is Variant type. The problems occurs only when I check this incorrect array element.

Upvotes: 0

Views: 1075

Answers (2)

Matt Cremeens
Matt Cremeens

Reputation: 5151

One approach among several would be to catch the error and handle it with some code further down.

On error goto HandleMyError
    'do something with vRangeShift(1, i)

'... more code here ....'
Cont:
    'finish out sub

Exit Sub 'Have this here so if there isn't an error, you can skip over
         'the error handler
HandleMyError:
    'if doing something with vRangeShift(1,i) produces an error, you 
    'you will end up here where you can handle the error as you see fit
    'once you've satisfactorily handled the error, you can return to 
    'that part of your code where you want the sub to continue
    Goto Cont

End Sub

Upvotes: 0

user6432984
user6432984

Reputation:

This should do the trick:

If IsError(vRangeShift(1, i)) Then

Upvotes: 4

Related Questions