[ create a new paste ] login | about

Link: http://codepad.org/yRauaTcp    [ raw code | fork ]

C, pasted on Jun 21:
Public Sub Encapsulate_IFERROR()
'Adds iferror to formula.
    
    Dim Row As Long
    Dim Col As Long
    Dim FormulaString As String
    Dim ReadArr As Variant
    
    If Selection.Cells.Count > 1 Then
    
        ReadArr = Selection.FormulaR1C1
        
        For Row = LBound(ReadArr, 1) To UBound(ReadArr, 1)
            For Col = LBound(ReadArr, 2) To UBound(ReadArr, 2)
            
                If Left(ReadArr(Row, Col), 1) = "=" Then
                If LCase(Left(ReadArr(Row, Col), 8)) <> "=iferror" Then
                    ReadArr(Row, Col) = "=iferror(" & Right(ReadArr(Row, Col), Len(ReadArr(Row, Col)) - 1) & ","""")"
                End If
                End If
            
            Next
        Next
        
        Selection.FormulaR1C1 = ReadArr
        
        Erase ReadArr
    
    Else
        
        FormulaString = Selection.FormulaR1C1
        
        If Left(FormulaString, 1) = "=" Then
        If LCase(Left(FormulaString, 8)) <> "=iferror" Then
        
            Selection.FormulaR1C1 = "=iferror(" & Right(FormulaString, Len(FormulaString) - 1) & ","""")"
        
        End If
        End If
    
    End If

End Sub


Create a new paste based on this one


Comments: