해결 M365윈도우11

배열함수 분산오류

12월 14일 조회 1,515

배열함수의 경우, 그 결과가 분산될 범위에 값들이 존재하면 오류가 나잖아요.

하나인경우는 "방해되는 셀 선택"해서 지우면 되는데, 여러개인 경우는 너무 번거롭네요.

그래서 매크로를 실행시켜서 분산될 범위에 값들을 한번에 지워줄 수 있을 것같은데 찾아봐도 없네요...

선택범위나 혹은 시트 전체의 배열함수의 분산범위를 확인하고 그 범위의 이미 존재하는 값들을 지우는 방법 있을까요?

#VBA·매크로 #배열수식

마

마법의손 채택된 답변 12월 15일

대충 만들어봤는데 사용해보세요..

Private Sub CleanSPILL_Func(ECell As Range)

On Error Resume Next
    
    Dim 수식 As String
    Dim Target As Range
        
    Set Target = ECell
    If Not Target.HasFormula Then Exit Sub
    수식 = Target.Formula2R1C1
    Target = "=rows(" & Mid(수식, 2) & ")"
    nRow = Target
    
    Target = "=columns(" & Mid(수식, 2) & ")"
    nCol = Target
    
    If Err.Number <> 0 Then
        Target.Formula2R1C1 = 수식
        Err.Clear
        Exit Sub
    End If
    
    If nRow >= 2 Or nCol >= 2 Then
        Target.Resize(nRow, nCol) = ""
    End If
    
    Target.Formula2R1C1 = 수식
    
End Sub



'목적 : SPILL 에러나는 영역 청소

Sub CleanSPILL()

Application.ScreenUpdating = False

    Dim ECell As Range
    
    If Not Selection.HasFormula Then
    
        For Each ECell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Cells
            CleanSPILL_Func ECell
        Next
    Else
        CleanSPILL_Func Selection
        
    End If

Application.ScreenUpdating = True


End Sub