엑셀 매크로 녹화 후 셀 위치가 변해도 작동하게 만드는 법 — 상대참조·테이블·Find/End 패턴
요약: 매크로 녹화만으로는 Range(“B3”) 같은 절대 주소가 박혀서 셀이 밀리면 곧바로 깨진다. 해법은 단순하다. ① 상대 참조로 녹화하고, ② 데이터를 표(ListObject)로 바꿔 구조적 참조를 쓰며, ③ 헤더를 찾아 접근하고, ④ CurrentRegion/End·R1C1로 범위를 동적으로 잡는 것이다. 아래 예제를 그대로 붙여 리팩터링하면 내일 배치가 바뀌어도 돌아간다.
1) 녹화 시 필수: ‘상대 참조’ & 데이터를 표로
개발 도구 탭에서 상대 참조 사용을 켜면 녹화기가 ActiveCell.Offset(…)로 기록한다. 여기에 데이터를 Ctrl+T로 표(ListObject)로 바꿔 두면 열/행 추가에도 자동 확장된다. 이후 구조적 참조 ListObjects("Sales").ListColumns("금액").DataBodyRange로 접근하면 헤더가 움직여도 안전하다.
2) 녹화 코드 리팩터링 — Select 제거가 1순위
녹화본은 보통 아래처럼 나온다.
' 녹화 결과(취약)
Range("B3").Select
Selection.Copy
Range("G3").Select
ActiveSheet.Paste이를 대상 직접 접근으로 바꾸면 위치 변화에 강해진다.
' 안전 버전(표+직접 접근)
Option Explicit
Sub CopyAmountToSummary()
Dim lo As ListObject, src As Range, dest As Range
Set lo = Sheets("Data").ListObjects("Sales")
Set src = lo.ListColumns("금액").DataBodyRange ' 헤더 이름으로 접근
Set dest = Sheets("Summary").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
dest.Resize(src.Rows.Count, 1).Value = src.Value ' 값만 복사
End Sub3) 헤더를 찾아 접근 — Find/Match 패턴
열 순서가 자주 바뀐다면 헤더 검색으로 열 인덱스를 구해 쓰자.
Function ColIndexByHeader(ws As Worksheet, headerText As String) As Long
Dim m
m = Application.Match(headerText, ws.Rows(1), 0)
If IsError(m) Then Err.Raise vbObjectError+1, , "헤더 없음: " & headerText
ColIndexByHeader = CLng(m)
End Function
Sub SumByHeader()
Dim ws As Worksheet: Set ws = Sheets("Data")
Dim ci As Long: ci = ColIndexByHeader(ws, "수량")
Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, ci).End(xlUp).Row
Sheets("Summary").Range("B2").Value = WorksheetFunction.Sum(ws.Range(ws.Cells(2, ci), ws.Cells(lastRow, ci)))
End Sub4) 행 끝·구간 자동 인식 — End/CurrentRegion·R1C1
' 현재 영역(빈 행 전까지) 루프
Sub ProcessRegion()
Dim rg As Range
Set rg = Sheets("Data").Range("A1").CurrentRegion
Dim r As Range
For Each r In rg.Resize(rg.Rows.Count - 1).Offset(1).Rows ' 헤더 제외
' r.Cells(1, "C") 처럼 상대 좌표 사용 가능
Next r
End Sub
' R1C1 기록을 활용(패턴 치환이 쉬움)
Sub FillNextColumnR1C1()
With Sheets("Data")
.Range("D2").Resize(.Cells(.Rows.Count, "C").End(xlUp).Row - 1, 1).FormulaR1C1 = "=RC[-1]*1.1"
End With
End Sub5) 성능·오류 처리 토글 — 켜고 끄기
Sub SafeRun()
Dim prevCalc As XlCalculation
On Error GoTo CleanUp
With Application
.ScreenUpdating = False
prevCalc = .Calculation: .Calculation = xlCalculationManual
.EnableEvents = False
End With
' --- 작업 본문 ---
CleanUp:
With Application
.ScreenUpdating = True
.Calculation = prevCalc
.EnableEvents = True
End With
If Err.Number <> 0 Then MsgBox "오류: " & Err.Description, vbExclamation
End Sub