[Resolved]Excel Index Function

[Bartender]

In addition to the other issue I'm have with ranges in the worksheet function Index that I posted in the thread "Excel Ranges," I have run into another problem which I can't figure out how to work around. I have to use an index on a block of cells, say B2:AL38, which has some numbers scattered in it, but a lot of blank cells as well.
The blank cells must remain blank.
When i use :
Application.Index([Sheet1!B2:AL38], row, col)
and it sees a blank cell, it gives me an object mismatch--- since it finds a string and wants a value---I think, at least. How can I make it think "zero" when it sees a blank cell?
How could I just do a :
If Application.Index(Inputs)='object mismatch' Then
Var=Var+0
Else
Var=Var+Application.Index(Inputs)
End If
?
How would I really write that?
Sigh.

Thanks for your help!

[RobDog888]

If Application.Index(Inputs)='object mismatch' Then

Would be trappable by using an error handling routine. Find the error number for that error and trap it like so.

Private Sub Something()
 
    On Error goto MyError
 
    'Blah, blah, blah....
 
    Exit Sub
MyError:
    If err.number = xxx Then
        'Something
    Else
        'Somethingelse
    End If
End Sub

[Bartender]

Thanks, I'll give that a try--put it at the end of my loop.
Thanks.

[Bartender]

Rob,
Quick question:
If I put a a couple On Error GoTo MyError's like so:

For ii = 1 to Size
On Error GoTo MyError
'error could happen here each loop
MyError:
'stuff
Next ii
For ii = 1 to Size
On Error GoTo MyError2
'Error can happen each loop
MyError2:
'stuff
Next ii

Can it recognize the end of one On Error statement and see the start of the next?

[RobDog888]

Its better to have your error handling in one place at the bottom and its not really used like a goto or gosub statement.

On Error GoTo MyError
For ii = 1 to Size
    'error could happen here each loop
Next ii
 
On Error GoTo MyError2
For ii = 1 to Size
    'Error can happen each loop
Next ii
 
On Error GoTo 0 'Turn off error handling.
'Do something
 
On Error GoTo MyError 'Turn it back on 
'do something
 
exit sub
 
MyError:
'stuff
exit sub
 
MyError2:
'stuff
exit sub

[Bartender]

Rob,
If I have an error, I want the loop to still continue. The error may recur, or it may not. In the even of an error, I just want to add 0 to my running total and continue as if nothing had happened.

For ii = 1 to 6
Val = Val + Application.Index(Inputs)
Next ii

If it went through errors, Val might end up containing "5 + 3 + Error + 2 + Error + 11"
So I would want it to read:
"5+3+0+2+0+11" = 21

But, you implied that it is wrong to say, for instance:

For ii = 1 to 6
On Error GoTo MyError
Val = Val +Application.Index(Inputs)
MyError:
Val = Val + 1
Next ii

I see how the myerror block might interrupt the code there, I'm not sure how it works...
Is it that the Loop wouldn't see the Next, since it would be 'part of' the MyError?

Theres no way to contain a MyError statement
MyError:
'Stuff
/MyError

...or something like that?

Thanks for helping.

[RobDog888]

Trap for error 13 - Type Mismatch error. Then do a Resume Next in the handler for that error.

On Error GoTo MyError
For ii = 1 to Size
    'error could happen here each loop
Next ii
 
On Error GoTo MyError2
For ii = 1 to Size
    'Error can happen each loop
Next ii
 
On Error GoTo 0 'Turn off error handling.
'Do something
 
On Error GoTo MyError 'Turn it back on 
'do something
 
exit sub
 
MyError:
    If err.Number = 13 then
        resume next
    else
        msgbox err.number & " - " & err.description
    Endif
exit sub
 
MyError2:
'stuff
exit sub