Replace zero array values with #N/A for chart

[NdNoviceHlp]

The objective is to not chart zero values in a chart with an array source. I would like to replace the array zero values with #N/A which should then be ignored by the chart but... I can't seem to stumble across the correct syntax to replace the array zero values to #N/A in a format that the chart will accept. I've trialed the code(s) below without success. Any assistance will be appreciated. Dave

'CRVArr contains an array of arrays with chart source data
For Cnt = LBound(CRVArr(Index)) To UBound(CRVArr(Index))
'CRVArr(Index)(Cnt) = Replace$(CRVArr(Index)(Cnt), "0", "#N/A")
'CPFTArr(Index)(Cnt) = Replace$(CPFTArr(Index)(Cnt), "0", "#N/A")
If CRVArr(Index)(Cnt) <> 0 Then
CRVArr(Index)(Cnt) = CRVArr(Index)(Cnt)
CPFTArr(Index)(Cnt) = CPFTArr(Index)(Cnt)
Else
CRVArr(Index)(Cnt) = "#N/A" '"NA()" 'CVErr(xlErrNA)
CPFTArr(Index)(Cnt) = "#N/A" '"NA()" 'CVErr(xlErrNA)
End If
Next Cnt

 

[RoryA]

Try cverr(xlerrna)

 

[NdNoviceHlp]

Thanks RoryA. I was a bit cryptic "I've trialed these code below"...

CRVArr(Index)(Cnt) = "#N/A" '"NA()" 'CVErr(xlErrNA)

I think that produces a number and the chart series wants the actual #N/A string as a value if that's possible? Dave

 

[NdNoviceHlp]

After some trial and error it seems like you can't insert the #N/A in an array in any useful charting way. To remove the zeroes from the array, it just requires creating a new array without the zeroes and then passing that array to the chart. Solved. Dave

Dim TArr1() As Variant, Tarr2() As Variant, Cnt As Integer, Cnt2 As Integer

For Cnt = 1 To SRsCnt
For Cnt2 = LBound(CRVArr(Cnt - 1)) To UBound(CRVArr(Cnt - 1))
If CRVArr(Cnt - 1)(Cnt2) <> 0 Then
ReDim Preserve TArr1(Cnt2 + 1)
ReDim Preserve Tarr2(Cnt2 + 1)
TArr1(Cnt2) = CRVArr(Cnt - 1)(Cnt2)
Tarr2(Cnt2) = CPFTArr(Cnt - 1)(Cnt2)
End If
Next Cnt2

With ObjWorksheetCRV.ChartObjects("CRVChart").Chart
.SeriesCollection.NewSeries
.SeriesCollection(Cnt).XValues = TArr1  'CRVArr(Cnt - 1)
.SeriesCollection(Cnt).Values = Tarr2  'CPFTArr(Cnt - 1)
'etc
End With
Next Cnt