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)

 

[shadyferret]

The code "dblRedLimit = CDbl(arrRedLimit(i))" didn't eliminate the type mismatch error, but the "IsError" worked! I don't know why that difference would take care of a type mismatch, but I'm still pretty new at VBA.

The rest of the code Norie suggests would likely work, but I don't know how much more efficient it would be. I'll play around with it though and find out.

Here's the final working code.

  'Find data value for Red Limit 
  arrRedLimit = ActiveChart.SeriesCollection("Upper Bound").Values
    While IsError(arrRedLimit(i))
    i = i - 1
    Wend
    dblRedLimit = arrRedLimit(i)

  Call Compare_SeriesCollection_Decrease

Thanks Norie, Mohammad Basem, and JLGWhiz for all of your help! :D
I'm not sure how to mark this "thread" as solved but I'll try.

 

[Norie]

Not sure about how efficient the code I posted but using For..Next is pretty similiar to using a While...Wend.

Mind you we don't know where i comes from in the code you've posted - are you looping backwards?

 

[shadyferret]

Ah, sorry. I was trying to thin out the code since it's actually a bit more elaborate than I was posting.

Yes, I'm looping backwards with "i" as a counter. I'm working with a total of 51 points per series, but most of them are currently blank or N/A as there's only 12 points of data (new data is added weekly). Its mostly a mindset thing, since the charts are based on x-amount of last points.

 

[Norie]

If you do want to loop backwards reverse the order of the For Next.

For I = UBound(arrRedLimit) To LBound(arrRedLimit) Step -1