☰

매크로를 사용하여 F9 키를 누르지 않은 값으로 셀 참조 바꾸기

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
매크로를 사용하여 F9 키를 누르지 않은 값으로 셀 참조 바꾸기

엑셀 매크로 질문입니다. 다음과 같은 일련의 공식이 주어졌습니다. 

= A1 + A2
= B2 + B2

등., ...

전체 셀을 바꾸지 않고 이러한 각 셀 참조를 값으로 바꾸고 싶습니다.

= 1 + 2
= 3 + 4

수동으로 수행하는 방법은 공식 내의 각 참조를 강조 표시하고 를 눌러 F9참조를 값으로 바꾸는 것입니다. 하지만 수천 개의 셀에 대해 이 작업을 수행해야 합니다. 이 작업을 자동으로 수행하는 매크로가 있나요?

답변1

다음 모듈은 범위를 사용하고 셀 참조를 참조된 셀에서 찾은 값으로 바꿉니다. Range.Precedents참조된 셀 주소를 문자열로 가져오고 Split이를 셀 주소 배열로 변환하는 데 사용됩니다 . 그런 다음 이 배열은 대상 셀의 수식에서 일치하는 텍스트를 찾아 바꾸는 데 사용됩니다.

용법

  • 전체 코드를 Visual Basic Editor의 표준 코드 모듈에 복사합니다.
  • 로 서브루틴을 호출합니다 Call ReplaceReferencesWithValues(yourRange).

매크로 Test는 서브루틴을 호출하여 선택한 범위의 참조를 대체합니다.

암호

Option Explicit


Sub Test()
    Call ReplaceReferencesWithValues(Selection)
End Sub


Sub ReplaceReferencesWithValues(rng As Range)
    Dim cl As Range
    Dim ws As Worksheet
    Dim strTemp As String
    Dim strRef() As String
    Dim intIndex As Integer

    For Each cl In rng
        Set ws = cl.Worksheet
        strTemp = cl.Formula
        strRef() = ReferenceArray(cl)

        For intIndex = LBound(strRef) To UBound(strRef)
            strTemp = Replace(strTemp, strRef(intIndex), _
                ws.Range(strRef(intIndex)).Value)
        Next

        cl.Formula = strTemp
    Next
End Sub


Function ReferenceArray(rngSource As Range) As Variant
    Dim rngRef As Range
    Dim strTemp As String
    On Error Resume Next

    For Each rngRef In rngSource.Precedents.Cells
        strTemp = strTemp & ", " & rngRef.Address(False, False)
    Next
    If Len(strTemp) > 0 Then strTemp = Mid(strTemp, 3)

    ReferenceArray = Split(strTemp, ", ")
End Function

우려사항

  • 교체는 String수식에 대한 참조가 모두 포함된 경우 및 그 자체로 발생합니다.셀"A1" 및 "A1"을 포함한 기타 텍스트인 경우 일치하는 텍스트도 다음 값으로 대체됩니다.셀"A1".
  • SUM참조를 셀 값으로 바꾸려고 하면 작동하는 데 범위가 필요한 수식(예: )이 중단됩니다.

답변2

@natancodes 솔루션이 가능해 보이지만 원래 질문은 텍스트의 텍스트를 대체하기 때문에 간단한 작업이 아닙니다. 다음과 같은 간단한 경우에 문제가 발생합니다. A1, A11, AA11 또는 AA1이 나타날 때 A1을 검색하는 수식은 잘못된 값으로 덮어쓰려고 시도하며 매크로는 작동하지 않습니다(On Error Resume Next 제외). 검색 및 교체 작업에 RegEx를 사용하는 것이 더 좋습니다. RegEx 표현식이 여기에 제공되지만 표현식에 대한 신뢰도 부족으로 인해 여전히 작동하는지 확실하지 않으며 추가 테스트가 필요합니다. ('?[a-zA-Z0-9\s[].]{1,99})?' ?!?$?[a-zA-Z]{1,3}$?[0-9]{1,7}(:$?[a-zA-Z]{1,3}$?[0- 9]{1,7})? (그것은에서 왔다https://www.get-digital-help.com/extract-cell-references-from-a-formula/).

다른 경로는 여기에 자세히 설명되어 있습니다.https://fastexcel.wordpress.com/2013/10/27/parsing-functions-from-excel-formulas-using-vba-is-mid-or-a-byte-array-the-best-method/, 약간 번거로워 보이지만 이러한 특별한 경우에는 작동할 수 있습니다.

관련 정보