Manipulating elements in Named Range

[chirayu]

Hi All,

I have a Named range that has hardcoded values inside it. These Values are populated from another Named Range whenever the macro is run.

What I would like to do is be able to manipulate values in the generated Named Range.

Sub Test()

'Create RngB from RngA - RngA is A1:A10
ThisWorkbook.Names.Add "RngB", [RngA].Value

'Here I need a code that can create an Array from RngB
'& then replace values basis whatever criteria I specify
'e.g. check each value & if #N/A then replace with 0

End Sub

 

[Hui]

Do it using formula in A1:A10 before loading the named range
in A1: =Iferror(current formula, 0)

 

[Luke M]

Can we just go to the source? By that, rather than have RngB based on RngA, and then try to extract from A, why not have our array pull from RngA directly?

Sub Test()
Dim myArray As Variant
Dim i As Long

myArray = [RngA].Value
For i = LBound(myArray) To UBound(myArray)
    If WorksheetFunction.IsError(myArray(i, 1)) Then
        myArray(i, 1) = 0
    End If
Next i

[RngA].Value = myArray

'Now we make B based on A

'Create RngB from RngA - RngA is A1:A10
ThisWorkbook.Names.Add "RngB", [RngA].Value

End Sub

 

[chirayu]

Thanks @Hui and @Luke M

Luke I made a few changes as I don't need to change the values of RngA. Directly load the correct values into RngB

Removed

[RngA].Value = myArray

Changed

ThisWorkbook.Names.Add "RngB", myArray

Going to use it with conjunction with this post to handle #N/A
http://chandoo.org/forum/threads/dynamic-chart-vba-smoother-animation.24866/

So main code in that becomes

Public Sub AnimateChart()

    Dim i As Integer
    Dim myArray As Variant
    Dim x As Long
       
    bFinished = False
   
    myArray = [chart_values]
    For x = LBound(myArray) To UBound(myArray)
        If WorksheetFunction.IsError(myArray(x, 1)) Then
            myArray(x, 1) = 0
        End If
    Next x
   
    ThisWorkbook.Names.Add "old", myArray
   
    ThisWorkbook.Names.Add "switch_to_record", [record_index].Value
    If [b_animation] Then
        For i = 1 To [animation_steps]
            [step] = i
            DoEvents
            If bFinished Then Exit For
        Next i
    End If
    bFinished = True

End Sub

 

[p45cal]

Going back to the first question, I think
ThisWorkbook.Names.Add "RngB", [IF(ISNA(RngA),0,RngA)]
does it.