엑셀에서 여러 영역에 걸쳐 검색어를 카운트하거나 데이터 비교를 할 때, 수작업으로 하다 보면 시간이 많이 걸립니다.
이번 글에서는 중복 범위, merged cell, 배열 크기 불일치 문제까지 안전하게 처리하면서, 검색어 × 영역 카운트를 고속으로 계산하는 VBA 코드를 소개합니다.
🔹 구현 목표
여러 검색어를 여러 영역에서 한 번에 검색하고 카운트
배열만으로 한 번에 출력 → 고속 처리
merged cell, 중복 범위, 시트 범위 초과, 1x1 범위 문제 안전 처리
출력 배열과 Range 열 수 불일치 방지
🔹 코드 구조
코드는 크게 7단계로 구성되어 있습니다.
검색어 목록 선택
검색 영역 선택
출력 위치 선택
결과 배열 준비
검색어 × 영역 카운트
출력 범위 안전하게 생성
결과 출력 및 서식 적용
각 단계는 안전성과 고속 처리에 초점을 맞춰 설계되었습니다.
🔹 핵심 안정 처리 포인트
merged cell 안전 처리 → startCell.MergeArea.Cells(1,1)
시트 범위 초과 방지 → lastRow / lastCol 조정
배열과 Range 크기 정확히 맞춤 → safeArr로 재조정
중복 범위, 1x1 범위, 다중 영역 모두 안전
고속 배열 처리 → For 루프 내부에서 출력하지 않고 배열 한 번에 출력
🔹 코드 예시
Public Sub Run_SearchCount(control As IRibbonControl)
Call 검색된회수
End Sub
Public Sub 검색된회수()
Dim searchList As Range
Dim searchAreas As Collection
Dim searchArea As Range
Dim outputCell As Range, startCell As Range
Dim arrSearchList As Variant
Dim arrArea As Variant
Dim tempArr As Variant
Dim resultArr() As Variant
Dim i As Long, r As Long
Dim cnt As Long
Dim nRows As Long, nCols As Long
'------------------------------------------------------------
' 1) 검색어 목록 선택
'------------------------------------------------------------
On Error Resume Next
Set searchList = Application.InputBox("검색어 목록 범위를 선택하세요", Type:=8)
On Error GoTo 0
If searchList Is Nothing Then Exit Sub
arrSearchList = searchList.Value2
' 1x1 처리
If Not IsArray(arrSearchList) Then
ReDim tempArr(1 To 1, 1 To 1)
tempArr(1, 1) = arrSearchList
arrSearchList = tempArr
End If
'------------------------------------------------------------
' 2) 검색 영역 선택
'------------------------------------------------------------
Set searchAreas = New Collection
On Error Resume Next
Set searchArea = Application.InputBox("첫 번째 검색 영역을 선택하세요", Type:=8)
On Error GoTo 0
If searchArea Is Nothing Then Exit Sub
searchAreas.Add GetRealDataRange(searchArea)
' 추가 영역 선택
Do
If MsgBox("추가 검색할 영역이 있습니까?", vbYesNo + vbQuestion) = vbNo Then Exit Do
On Error Resume Next
Set searchArea = Application.InputBox("추가 검색 영역을 선택하세요", Type:=8)
On Error GoTo 0
If Not searchArea Is Nothing Then searchAreas.Add GetRealDataRange(searchArea)
Loop
If searchAreas.Count < 1 Then
MsgBox "검색 영역이 없습니다.", vbExclamation
Exit Sub
End If
'------------------------------------------------------------
' 3) 출력 위치 선택
'------------------------------------------------------------
On Error Resume Next
Set outputCell = Application.InputBox("출력 시작 위치를 선택하세요", Type:=8)
On Error GoTo 0
If outputCell Is Nothing Then Exit Sub
Set startCell = outputCell.Cells(1, 1)
' merged cell 처리
If startCell.MergeCells Then
Set startCell = startCell.MergeArea.Cells(1, 1)
End If
If startCell Is Nothing Then
MsgBox "출력 위치가 올바르지 않습니다.", vbExclamation
Exit Sub
End If
'------------------------------------------------------------
' 4) 결과 배열 준비 (1-based)
'------------------------------------------------------------
nRows = UBound(arrSearchList, 1) - LBound(arrSearchList, 1) + 2 ' +1 헤더
nCols = searchAreas.Count + 1
ReDim resultArr(1 To nRows, 1 To nCols)
' 헤더
resultArr(1, 1) = "검색어"
For i = 1 To searchAreas.Count
resultArr(1, i + 1) = "영역" & i
Next i
'------------------------------------------------------------
' 5) 검색어 × 영역 카운트
'------------------------------------------------------------
For r = 1 To UBound(arrSearchList, 1)
If Trim(CStr(arrSearchList(r, 1))) <> "" Then
resultArr(r + 1, 1) = arrSearchList(r, 1)
For i = 1 To searchAreas.Count
arrArea = searchAreas(i).Value2
' 1x1 처리
If Not IsArray(arrArea) Then
ReDim tempArr(1 To 1, 1 To 1)
tempArr(1, 1) = arrArea
arrArea = tempArr
End If
' 카운트
cnt = Application.WorksheetFunction.CountIf(searchAreas(i), arrSearchList(r, 1))
resultArr(r + 1, i + 1) = cnt
Next i
End If
Next r
'------------------------------------------------------------
' 6) 출력 범위 안전하게 생성
'------------------------------------------------------------
Dim ws As Worksheet
Dim outputRng As Range
Dim lastRow As Long, lastCol As Long
Dim actualRows As Long, actualCols As Long
Dim safeArr() As Variant
Set ws = startCell.Worksheet
lastRow = WorksheetFunction.Min(startCell.Row + nRows - 1, ws.Rows.Count)
lastCol = WorksheetFunction.Min(startCell.Column + nCols - 1, ws.Columns.Count)
On Error Resume Next
Set outputRng = ws.Range(startCell, ws.Cells(lastRow, lastCol))
On Error GoTo 0
If outputRng Is Nothing Then
MsgBox "출력 범위 생성 실패!", vbCritical
Exit Sub
End If
' Range 크기 가져오기
actualRows = outputRng.Rows.Count
actualCols = outputRng.Columns.Count
' 배열을 Range 크기에 맞게 재생성
ReDim safeArr(1 To actualRows, 1 To actualCols)
' 기존 배열 값 복사
For r = 1 To WorksheetFunction.Min(nRows, actualRows)
For i = 1 To WorksheetFunction.Min(nCols, actualCols)
safeArr(r, i) = resultArr(r, i)
Next i
Next r
'------------------------------------------------------------
' 7) 결과 출력
'------------------------------------------------------------
outputRng.Value = safeArr
outputRng.HorizontalAlignment = xlRight
If ws Is outputRng.Worksheet Then outputRng.Columns.AutoFit
MsgBox "검색 완료되었습니다! (모든 안전 체크 완료)", vbInformation
End Sub
'------------------------------------------------------------
' 실제 데이터 범위 추출 함수
'------------------------------------------------------------
Function GetRealDataRange(r As Range) As Range
Dim ws As Worksheet
Dim lastRow As Long
Set ws = r.Worksheet
If r.Rows.Count = ws.Rows.Count Then
lastRow = ws.Cells(ws.Rows.Count, r.Column).End(xlUp).Row
Set GetRealDataRange = ws.Range(ws.Cells(1, r.Column), ws.Cells(lastRow, r.Column))
Else
Set GetRealDataRange = r
End If
End Function
코드 전체를 복사하여 VBA 편집기(Module)에 붙여넣으면 바로 사용 가능합니다.
🔹 사용법 예시
검색어 목록 선택 → 단일 열 범위를 선택합니다.
검색 영역 선택 → 여러 영역 선택 가능
출력 위치 선택 → 단일 셀 기준
실행 → 검색어별 영역별 카운트가 출력됩니다
🔹 장점
반복 업무를 몇 초 만에 처리
Excel에서 흔히 발생하는 오류 상황 모두 안전하게 처리
배열 한 번에 출력 → 성능 최적화
실무에서 바로 적용 가능한 안정적인 구조
🔹 마무리
이제 엑셀에서 대용량 데이터 검색과 카운트를 처리할 때, 더 이상 복잡한 수식이나 반복 수작업에 시간을 낭비하지 않아도 됩니다.
이 코드는 merged cell, 중복 범위, 열 수 불일치 등 Excel에서 자주 발생하는 문제를 모두 대비하여 안정적으로 작동합니다.
ps 파일을 추가기능으로 설치 하시면 버튼이 생성되어 편리합니다