Excel VBA에서 찾기 및 바꾸기 사용하기

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Younjung Kim

Last updated on 3월 24, 2023

이 튜토리얼에서는 Excel VBA에서 찾기 및 바꾸기 메서드를 사용하는 방법을 보여드립니다.

VBA Find

Excel에는 찾기 및 찾기 및 바꾸기 도구가 있습니다.

이 도구는 바로 가기 키인 CTRL + F(찾기) 또는 CTRL + H(바꾸기)를 사용하거나 리본 메뉴를 통해 활성화할 수 있습니다: 홈 > 편집 > 찾기 및 선택에서 활성화할 수 있습니다.

옵션을 클릭하면 고급 검색 옵션을 볼 수 있습니다:

VBA를 사용하여 이러한 방법에 쉽게 액세스할 수 있습니다.

VBA Find 예제

찾기 기능을 시연하기 위해 Sheet1에 다음 데이터 집합을 만들었습니다.

예제를 함께 진행 하려면 직접 통합 문서에 데이터를 입력해 보세요.

선택적 매개 변수가 없는 VBA Find 기능 활용하기

VBA 찾기 메서드를 사용할 때 설정할 수 있는 다양한 선택적 매개 변수가 있습니다.

Find 메서드를 사용할 때마다 모든 매개 변수를 정의할 것을 강력히 권장합니다!

선택적 매개 변수를 정의하지 않으면 VBA는 Excel의 찾기 창에서 현재 선택된 매개 변수를 사용합니다. 즉, 코드가 실행될 때 어떤 검색 매개변수가 사용되고 있는지 알 수 없습니다. Find는 전체 통합 문서 또는 시트에서 실행될 수 있습니다. 수식이나 값을 검색할 수도 있습니다. Excel의 찾기 창에서 현재 선택된 항목을 수동으로 확인하지 않는 한 알 수 있는 방법이 없습니다.

간단하게 설명하기 위해 선택적 매개변수가 정의되지 않은 예제부터 시작하겠습니다.

간단한 찾기 예제

간단한 찾기 예제를 살펴보겠습니다:

Sub TestFind()
Dim MyRange As Range

Set MyRange = Sheets("Sheet1").UsedRange.Find("employee")
MsgBox MyRange.Address
MsgBox MyRange.Column
MsgBox MyRange.Row

End Sub

이 코드는 Sheet1의 Used Range에서 “employee”를 검색합니다. “employee”를 찾으면 첫 번째로 찾은 값의 정보를 Range 변수인 MyRange에 할당합니다.

그런 다음 검색된 텍스트의 주소, 열 및 행이 포함된 메시지 박스를 표시합니다.

이 예제에서는 기본값으로 설정된 찾기 옵션들이 사용됩니다(Excel의 찾기 창에서 변경하지 않았다고 가정합니다):

검색하하는 텍스트가 셀 값과 부분적으로 일치합니다(정확한 셀 일치는 필요하지 않음).
대소문자를 구분하지 않습니다.
단일 워크시트내에서만 검색합니다.

이러한 설정은 다양한 선택적 매개변수(아래 설명 참조)를 사용하여 변경할 수 있습니다.

Find 메서드 참고 사항

Find 메서드는 실행 결과로 텍스트가 있는 셀을 선택하지 않습니다. 코드에서 조작할 수 있는 검색된 범위만 식별합니다.
Find 메서드는 처음 발견된 인스턴스만 찾습니다.
와일드카드(*)를 사용할 수 있습니다(예: ‘E*’ 검색).

Nothing Found(검색 결과 없음)

검색 텍스트가 존재하지 않으면 Range 객체가 비어 있는 상태로 유지됩니다. 위치 값이 존재하지 않기 때문에 코드에서 검색 결과에 대한 위치 값을 표시하려고 할 때 큰 문제를 일으킵니다. 이럴 경우 오류 메시지가 표시됩니다.

다행히도 VBA 내에서 Is 연산자를 사용하여 범위 객체가 비어있는지 확인할 수 있습니다:

If Not MyRange Is Nothing Then

위에 사용된 예제코드에 해당 코드를 추가해 보겠습니다:

Sub TestFind()
Dim MyRange As Range

Set MyRange = Sheets("Sheet1").UsedRange.Find("employee")
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
    MsgBox MyRange.Column
    MsgBox MyRange.Row
Else
    MsgBox "검색 결과가 없습니다"
End If
End Sub

Find 매개변수

지금까지는 찾기 메서드를 사용하는 기본적인 예제만 살펴봤습니다. 그러나 검색을 구체화하는 데 도움이 되는 여러 가지 선택적 매개변수를 사용할 수도 있습니다.

매개변수	유형	설명	값
What	필수	검색할 문자열	문자열이나 숫자 같은 모든 데이터 유형
After	선택사항	검색을 시작할 셀	셀 주소
LookIn	선택사항	검색에 수식, 값, 코맨트 사용	xlValues, xlFormulas, xlComments
LookAt	선택사항	부분일치 또는 전체 일치	xlWhole, xlPart
SearchOrder	선택사항	검색할 순서 – 행 또는 열	xlByRows, xlByColummns
SearchDirection	선택사항	검색할 방향 – 순방향 또는 역방향	xlNext, xlPrevious
MatchCase	선택사항	대소문자 구분 여부	True 또는 False
MatchByte	선택사항	더블 바이트 문자 지원을 설치한 경우에만 사용(예: 중국어)	True 또는 False
SearchFormat	선택사항	셀 서식으로 검색 허용	True 또는 False

After 매개변수와 여러 값 찾기

After 매개변수를 사용하여 검색 시작 셀을 지정할 수 있습니다. 이는 검색하려는 값의 결과가 두 개 이상 있는 경우에 유용합니다.

검색에서 이미 하나의 값을 찾았는데 더 많은 값을 찾을 수 있다는 것을 알고 있는 경우 ‘After’ 매개변수와 함께 Find 메서드를 사용하여 첫 번째 결과를 기록한 다음 해당 셀을 다음 검색의 시작점으로 사용할 수 있습니다.

이를 사용하여 검색 텍스트의 여러 결과를 찾을 수 있습니다:

Sub TestMultipleFinds()
Dim MyRange As Range, OldRange As Range, FindStr As String

'첫번째 검색"‘Light & Heat"
Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat")

'검색결과가 없을 경우 프로시저 종료
If MyRange Is Nothing Then Exit Sub

'첫 번째 검색결과의 주소 표시하기
MsgBox MyRange.Address

'Range 객체의 복사본을 만듭니다.
Set OldRange = MyRange

'문자열에 "|" 문자로 구분하는 주소를 추가합니다.
FindStr = FindStr & "|" & MyRange.Address

'추가 결과가 있는지 반복적으로 검색합니다.
Do
    '이전에 찾은 주소를 After 매개 변수로 사용하여 'Light & Heat'를 검색합니다. 
    Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))

    '주소가 이미 발견된 경우 do 루프를 종료합니다. 이렇게 하면 반복문이 중지됩니다.
    If InStr(FindStr, MyRange.Address) Then Exit Do
    
    '찾은 주소를 표시합니다.
    MsgBox MyRange.Address

    '주소 문자열에 최신 주소를 추가합니다. ("|"로 구분)
    FindStr = FindStr & "|" & MyRange.Address

    '검색 결과를 저장합니다(다음 루프에서 After 매개변수에 사용하기 위함입니다)
     Set OldRange = MyRange
Loop
End Sub

이 코드는 UsedRange 내에서 반복적으로 ‘Light & Heat’를 검색하고 찾을 때마다 주소를 표시합니다.

이 코드는 FindStr에서 중복된 주소가 발견될 때까지 계속 반복되며, 이 경우 Do 루프를 종료합니다.

LookIn 매개변수

LookIn 매개변수를 사용하여 검색할 셀의 구성 요소를 지정할 수 있습니다. 검색옵션으로 셀의 값, 수식 또는 주석을 지정할 수 있습니다.

xlValues – 셀 값(계산 후 셀의 최종 값)을 검색합니다.
xlFormulas – 셀에 입력된 함수 자체(셀에 입력된 모든 내용) 내에서 검색합니다.
xlComments – 노트 내에서 검색합니다.
xlCommentsThreaded – 메모 내에서 검색합니다.

워크시트에 수식이 입력되어 있다고 가정하면 아래의 예제 코드를 사용하여 수식의 첫 번째 위치를 찾을 수 있습니다:

Sub TestLookIn()
Dim MyRange As Range

Set MyRange = Sheets("Sheet1").UsedRange.Find("=", LookIn:=xlFormulas)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address   
Else
    MsgBox "검색 결과 없음"
 End If
End Sub

‘LookIn’ 매개변수가 xlValues로 설정된 경우 코드에 ‘검색 결과 없음’ 메시지가 표시됩니다. 이 예제에서는 B10을 반환합니다.

LookAt 매개변수 사용하기

LookAt 매개변수는 찾기에서 정확히 일치하는 셀을 검색할지, 아니면 검색 값이 포함된 모든 셀을 검색할지 결정합니다.

xlWhole – 전체 셀이 검색 값과 일치해야 합니다.
xlPart – 셀 내에서 검색 문자열을 검색합니다. 부분 일치하는 셀들을 검색합니다.

이 코드 예제는 “light” 텍스트가 포함된 첫 번째 셀을 찾습니다. Lookat:=xlPart를 사용하면 위의 예제에서 “Light & Heat”를 검색한 결과와 일치하는 항목을 반환합니다.

Sub TestLookAt()
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").UsedRange.Find("light", Lookat:=xlPart)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
  Else
    MsgBox "검색 결과 없음"  
End If
End Sub

xlWhole이 설정된 경우 셀 값이 “light”인 경우에만 일치 항목이 반환됩니다.

SearchOrder 매개변수

SearchOrder 매개변수는 범위 전체에서 검색을 수행하는 방법을 지정합니다.

xlRows – 행 단위로 검색 수행
xlColumns – 열 단위로 검색 수행

Sub TestSearchOrder()
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").UsedRange.Find("employee", SearchOrder:=xlColumns)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
Else
    MsgBox "검색 결과 없음"
End If
End Sub

이는 어떤 항목이 먼저 검색될지에 영향을 줍니다.

앞서 워크시트에 입력한 샘플 데이터를 사용했을 경우, SearchOrder가 xlColumns인 경우 찾은 셀은 A5입니다. SearchOrder를 xlRows로 변경할 경우 찾은 셀은 C4입니다.

SearchOrder 매개변수는 검색 범위 내에 중복된 값이 있고 특정 열에서 첫 번째 검색결과를 찾으려는 경우에 유용합니다.

SearchDirection 매개변수

SearchDirection 매개변수는 검색을 진행할 방향(빠르게 검색하기 위해서, 순방향 또는 역방향)을 지정합니다.

xlNext – 범위에서 순방향으로 일치하는 값 검색
xlPrevious – 범위에서 역방향으로 일치하는 값 검색

마찬가지로, 검색 범위 내에 중복된 값이 있는 경우 어떤 값이 먼저 발견되는지에 영향을 미칠 수 있습니다.

Sub TestSearchDirection()
Dim MyRange As Range

Set MyRange = Sheets("Sheet1").UsedRange.Find("heat", SearchDirection:=xlPrevious)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
Else
    MsgBox "검색 결과 없음"
End If
End Sub

샘플 데이터에 이 코드를 사용하면 검색 방향이 xlPrevious인 경우 C9의 위치가 반환됩니다. xlNext 매개 변수를 사용하면 A4의 위치가 반환됩니다.

Next 매개 변수는 검색 범위의 왼쪽 상단 모서리에서 검색을 시작하여 아래쪽으로 작업한다는 것을 의미합니다. Previous 매개변수는 검색 범위의 오른쪽 하단에서 검색을 시작하여 위쪽으로 검색을 수행합니다.

MatchByte 매개변수

MatchBye 매개변수는 중국어, 러시아어, 일본어와 같이 각 문자를 표현하는 데 더블 바이트를 사용하는 언어에만 사용됩니다.

이 매개변수를 ‘True’로 설정하면 Find는 더블바이트 문자와 더블바이트 문자만 일치시켜서 검색합니다. 이 매개변수를 ‘False’로 설정하면 더블바이트 문자가 싱글바이트 또는 더블바이트 문자와 일치시켜서 검색합니다.

SearchFormat 매개변수

SearchFormat parameter를사용하면 셀서식이 일치하는 셀을 검색할 수 있습니다. 사용 중인 특정 글꼴, 굵은 글꼴, 텍스트 색상 등이 검색 대상일 수 있습니다. 이 매개변수를 사용하기 전에 Application.FindFormat 속성을 사용하여 검색에 필요한 형식을 설정해야 합니다.

관련된 예제는 아래와 같습니다:

Sub TestSearchFormat()
Dim MyRange As Range

Application.FindFormat.Clear
Application.FindFormat.Font.Bold = True
Set MyRange = Sheets("Sheet1").UsedRange.Find("heat", Searchformat:=True)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
Else
    MsgBox "검색 결과 없음"
End If
Application.FindFormat.Clear
End Sub

이 예제에서는 굵은 글꼴을 찾도록 FindFormat 속성이 설정되어 있습니다. 그런 다음 Find 문은 글꼴이 굵은 글꼴인 경우에만 해당 텍스트의 검색결과를 반환하도록 SearchFormat 매개 변수를 True로 설정하여 ‘heat’라는 단어를 검색합니다.

앞서 표시된 샘플 데이터에서는 굵은 글꼴로 ‘heat’이라는 단어가 포함된 유일한 셀인 A9셀이 반환됩니다.

코드 끝 FindFormat 속성을 지우는 코드가 포함되어있는지 확인하세요. 그렇지 않으면 다음 검색에서 여전히 이 속성을 고려하여 잘못된 결과를 반환합니다.

SearchFormat 매개변수를 사용하는 경우 와일드카드(*)를 검색 값으로 사용할 수도 있습니다. 이 경우 굵은 글꼴로 모든 값을 검색합니다:

Set MyRange = Sheets("Sheet1").UsedRange.Find("*", Searchformat:=True)

여러 매개 변수 사용

여기에 설명된 모든 Find 매개 변수는 필요한 경우 서로 조합하여 사용할 수 있습니다.

예를 들어, ‘LookIn’ 매개변수와 ‘MatchCase’ 매개변수를 결합하여 셀 텍스트 전체를 확인하면서 대소문자를 구분하도록 설정할 수 있습니다.

Sub TestMultipleParameters()
Dim MyRange As Range
Set MyRange = Sheets("Sheet1").UsedRange.Find("Light & Heat", LookAt:=xlWhole, MatchCase:=True)
If Not MyRange Is Nothing Then
    MsgBox MyRange.Address
Else
    MsgBox "검색 결과 없음"
End If
End Sub

이 예제에서는 A4를 반환하지만, 텍스트의 일부(예: ‘heat’)만 검색어로 사용했다면 셀 값 전체와 일치하기 때문에 아무것도 찾을 수 없습니다. 또한 대소문자가 일치하지 않아서 검색 결과는 없음으로 출력됩니다.

Set MyRange = Sheets("Sheet1").UsedRange.Find("heat", LookAt:=xlWhole, MatchCase:=True)

Excel VBA에서 바꾸기(Replace)

Excel VBA에는 ‘찾기’와 매우 유사한 방식으로 작동하지만 찾은 셀 위치의 값을 새 값으로 바꾸는 바꾸기 함수가 있습니다.

다음은 Replace 메서드 문에서 사용할 수 있는 매개 변수입니다. 이들은 Find 메서드 문과 정확히 동일한 방식으로 작동합니다. ‘찾기’와 유일한 차이점은 대체 매개 변수를 지정해야 한다는 점입니다.

이름	유형	설명	값
What	필수	검색할 값	문자열 또는 숫자와 같은 모든 데이터 유형
Replacement	필수	대체할 값	문자열 또는 숫자와 같은 모든 데이터 유형
LookAt	선택사항	셀의 일부 일치 또는 셀 전체일치	xlPart 또는 xlWhole
SearchOrder	선택사항	검색할 순서 – 행 또는 열	xlByRows 또는 xlByColumns
MatchCase	선택사항	대/소문자 구분 검색 여부	True 또는 False
MatchByte	선택사항	더블 바이트 언어 지원을 설치한 경우에만 사용됩니다.	True 또는 False
SearchFormat	선택사항	셀 서식을 활용한 검색 허용	True 또는 False
ReplaceFormat	선택사항	검색할 셀의 서식	True 또는 False

ReplaceFormat 매개 변수는 SearchFormat 매개 변수가 Find 메서드에서 작동하는 것과 동일한 방식으로 특정 형식(예: 굵게)을 가진 셀을 검색합니다. 앞서 표시된 Find 예제 코드에서와 같이 먼저 Application.FindFormat 속성을 설정해야 합니다.

선택적 매개변수 없이 바꾸기

가장 간단하게는 검색 중인 항목과 이를 대체할 항목만 지정하면 됩니다.

Sub TestReplace()
Sheets("Sheet1").UsedRange.Replace What:="Light & Heat", Replacement:="L & H"
End Sub

Find 메서드는 일치하는 값의 첫 번째 인스턴스만 반환하는 반면, Replace 메서드는 지정된 전체 범위에서 작동하며 일치하는 값을 찾은 모든 것을 바꿉니다.

여기에 표시된 바꾸기 코드는 UsedRange 개체에 의해 정의된 전체 셀 범위에서 ‘Light & Heat’의 모든 인스턴스를 ‘L & H’로 바꿉니다.

VBA 텍스트 문자열 내에서 텍스트 찾기 또는 바꾸기

위의 예제는 VBA를 사용하여 Excel 데이터와 상호 작용할 때 훌륭하게 작동합니다. 그러나 VBA 문자열과 상호 작용하려면 INSTR 및 REPLACE와 같은 기본 제공 VBA 함수를 사용할 수 있습니다.

INSTR 함수를 사용하여 긴 문자열 내에서 텍스트 문자열을 찾을 수 있습니다.

Sub TestInstr()
MsgBox InStr("This is MyText string", "MyText")
End Sub

이 예제 코드는 검색할 문자열에서 ‘MyText’가 발견된 위치인 9를 반환합니다.

대소문자를 구분한다는 점에 유의하세요. ‘MyText’가 모두 소문자인 경우 검색 문자열을 찾지 못했음을 의미하는 0 값이 반환됩니다. 아래에서 대소문자 구분을 비활성화하는 방법에 대해 설명합니다.

INSTR – 검색 시작 지점

두 가지 추가 옵션 매개변수를 사용할 수 있습니다. 검색 시작 지점을 지정할 수 있습니다:

MsgBox InStr(9, "This is MyText string", "MyText")

시작점이 9로 지정되어 있으므로 여전히 9를 반환합니다. 시작점이 10이면 10 이후에 일치하는 항목이 없으므로 0(일치 항목 없음)을 반환합니다.

INSTR – 대/소문자 구분

비교 매개 변수를 vbBinaryCompare 또는 vbTextCompare로 설정할 수도 있습니다. 이 매개 변수를 설정하는 경우 검색 시작 지점 매개 변수 값이 있어야 합니다.

vbBinaryCompare – 대/소문자 구분(기본값)
vbTextCompare – 대소문자 구분 없음

MsgBox InStr(1, "This is MyText string", "mytext", vbTextCompare)

이 구문은 검색 텍스트가 소문자인 경우에도 여전히 9를 반환합니다.

대소문자 구분을 비활성화하려면 코드 모듈 상단에 텍스트 비교 옵션( Option Compare Text )을 선언할 수도 있습니다.

VBA Replace 함수

문자열의 문자를 코드 내에서 다른 텍스트로 바꾸려면 Replace 메서드를 사용해야합니다:

Sub TestReplace()
MsgBox Replace("This is MyText string", "MyText", "My Text")
End Sub

이 코드는 ‘MyText’를 ‘My Text’로 바꿉니다. vbBinaryCompare가 기본값이므로 검색 문자열은 대소문자를 구분합니다.

다른 선택적 매개변수를 추가할 수도 있습니다:

Start – 반환할 문자열의 시작지점 위치를 정의합니다. 찾기 메서드와 달리 Start 매개변수에 정의된 문자 번호부터 시작하여 잘린 문자열을 반환합니다.
Count – 수행할 바꾸기 횟수를 정의합니다. 기본적으로 바꾸기는 발견된 검색 텍스트의 모든 인스턴스를 변경하지만 Count 매개 변수를 1로 설정하여 한 번만 바꾸도록 제한할 수 있습니다.
Compare – Find 메서드에서와 마찬가지로 vbBinaryCompare 또는 vbTextCompare를 사용하여 이진 검색 또는 텍스트 검색을 지정할 수 있습니다. vbBinaryCompare는 대소문자를 구분하고 vbTextCompare는 대소문자를 구분하지 않습니다.

MsgBox Replace("This is MyText string (mytext)", "MyText", "My Text", 9, 1, vbTextCompare)

이 코드는 ‘My Text string (mytext)’을 반환합니다. 시작점이 9이므로 새로 반환되는 문자열은 9번째 문자열부터 시작하기 때문입니다. Count 매개 변수가 1로 설정되어 있기 때문에 첫 번째 ‘MyText’만 변경되었습니다.

바꾸기 메서드는 예를들면 O’Flynn과 같이 아포스트로피가 포함된 사람 이름과 같은 문제를 해결하는 데 이상적입니다. 작은따옴표를 사용하여 문자열 값을 정의할 때 아포스트로피가 있는 경우 코드가 아포스트로피를 문자열의 끝으로 해석하고 나머지 문자열을 인식하지 못하기 때문에 오류가 발생할 수 있습니다.

바꾸기 메서드를 사용하여 아포스트로피를 아무것도 없는 것으로 바꾸어 완전히 제거할 수 있습니다.