While Loop with Type Mismatch

[shadyferret]

I have a while loop that counts backwards in a seriescollection to find the last value with data. In one particular case, I had to use an IF formula in the source data where it would output NA() if the statment was false, so instead of loop through empty strings until the loop finds a number, it loops through #NA until it finds a number.

This ALMOST works. In Debug mode, the loop actually returns a value of 141, which is what I want, but it won't store it in a variable. Help?

Code:
Dim arrRedLimit As Variant
Dim dblRedLimit As Double
Dim i As Long
Dim varErr As Variant

varErr = CVErr(xlErrNA)

arrRedLimit = ActiveChart.SeriesCollection("Upper Bound").Values
While IsEmpty(arrRedLimit(i))
i = i - 1
Wend
dblRedLimit = arrRedLimit(i)

Can anyone see the error?


(P.S. If you're wondering why I would have "NA" as an output, its because it won't graph, where putting "" for my false condition did)

 

[JLGWhiz]

I was thinking that maybe th i = i- 1 might cause the value of i to change for the statement dblRedLimit = arrRedLimit(i), but I am not sure. That is just a first glance. If it does, then that would make arrRedLimit(i) = something other than the first found value, or Nothing, depending on what the series collection held. I haven't used While in a long time, so not sure when it would acturally kick out of the loop. I will have to check that.

 

[JLGWhiz]

That was not the answer. It pops right out of the loop as soon as it hits a value. So, the only other thing I can think of is to add the property .Value like:

dblRedLimit = arrRedLimit(i).Value

 

[shadyferret]

I'll try that. The code works fine for all of my other series, but not the one with "NA()" in it, so I'm not sure if that will fix it.

Thanks for your help!

 

[Mohammad Basem]

In this line

While IsEmpty(arrRedLimit(i))

you are checking empty values but not the #N/A error.

Try something like

While IsEmpty(arrRedLimit(i)) Or arrRedLimit(i)=VarErr

 

[shadyferret]

Oh oops, I should have better checked what I was coping. I did that and its how I got the right number (the 141) but it won't store the value as a variable.

 

[Mohammad Basem]

What do you mean by

...but it won't store the value as a variable.

and what about the statement

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]dblRedLimit = arrRedLimit(i)[/COLOR][/SIZE][/FONT]

Does this line generate error or what?

 

[shadyferret]

What do you mean by

and what about the statement

[FONT=Consolas][SIZE=2][COLOR=navy]dblRedLimit = arrRedLimit(i)[/COLOR][/SIZE][/FONT]

Does this line generate error or what?

Yes, this line has an error, although I'm not sure why. In Debug mode, hovering over arrRedLimit(i) in that line shows the number 141, but hovering over dblRedLimit here and the next its used shows that dblRedLimit is empty. The program terminates because of a type mismatch. I've delcared dblRedLimit as Long, so I'm not sure what's going on.

 

[Mohammad Basem]

Did you try dblRedLimit = CDbl(arrRedLimit(i))?

Is it possible to post sample data with the code?

 

[Norie]

Have you tried using IsError?

For I = LBound(arrRedLimit) To UBound(arrRedLimit)
    If IsError(arrRedLimit) Then Exit For
Next I

The For will be exited when the first error value, ie NA, is found.

The loop variable, I, will actually point to the error but If we assume the value before that isn't an error we can return it with I-1.

So after the loop.

dblRedLimit = arrRedLimit(I-1)