VBA code to erase an array element returning Error 2042 ???

[Patachoup]

Hello,

How to easily erase an single array element returning "Error 2042" using VBA
code ?

Thx in advance

 

[Ben McBen]

I think we need some more background - is it a 1 or 2 dim array, do you
merely wnt to blank out the entry or actually delete it.

If its 1 dimensional, then the "Filter" function should be useful. If its
2d I think you will have to resort to more VBA.

Please also see:

http://www.ozgrid.com/forum/showthread.php?t=30439

 

[Patachoup]

hello,

below is my code, I need to return Min and Max Values, but it doesn't work
if any value in the array returns error2042:

Thx

-----------------------
For iSrs = 1 To nSrs
'these are chart series
Yvals = ActiveChart.SeriesCollection(iSrs).Values
nVals = ActiveChart.SeriesCollection(iSrs).Points.Count

'test every single point in the serie
For iVals = 1 To nVals
If IsError(Yvals(iVals)) Then
Yvals.Points(iVals).Delete 'HERE is the line I
need to change!
End If
Next

Ymax(iSrs) = WorksheetFunction.max(Yvals)
Ymin(iSrs) = WorksheetFunction.min(Yvals)
Next

 

[Ben McBen]

There are various approaches, but I think it would be best if you just change
the underlying data - by wrapping you vlookup in an "if" to eliminate any
errors....

 

[Peter T]

Probably easier to make another array for your min/max functions

Untested -

' other declarations
Dim nCnt As Long
Dim arrVals() As Double

For iSrs = 1 To nSrs
'these are chart series

yVals = ActiveChart.SeriesCollection(iSrs).Values
nVals = ActiveChart.SeriesCollection(iSrs).Points.Count
nVals = UBound(yVals)
nCnt = 0
ReDim arrVals(1 To nVals)
'test every single point in the serie
For iVals = 1 To UBound(yVals)
If Not IsError(yVals(iVals)) Then
nCnt = nCnt + 1
arrVals(nCnt) = yVals(iVals)
End If
Next
If nCnt < nVals Then
ReDim Preserve arrVals(1 To nCnt)
End If

Ymax(iSrs) = WorksheetFunction.Max(arrVals)
Ymin(iSrs) = WorksheetFunction.Min(arrVals)
Next

Although Worksheet functions are extremly fast in cells they are not so fast
when called in VBA. I suspect it would be quicker to get your min/max values
directly in the loop, which would also avoid the need to make the second
array.

Regards,
Peter T