Excel에서 변수 행 번호로 VBA 범위 활용하기 (실전 예제 4가지)

이 글에서는 Excel에서 변수 행 번호를 사용하여 VBA 범위를 다루는 방법을 소개합니다. VBA 범위를 활용해 셀 범위를 선택하고, 셀에 숫자를 입력하고, 셀 범위에 수학 연산을 수행하며, 셀에 색상을 입히는 방법까지 모두 변수 행 번호와 함께 익힐 수 있습니다.

VBA 코드 미리 보기: 변수 행 번호로 범위 설정하기

Sub Range_with_Variable_Row_Number()

First_Cell = InputBox("Enter the First Cell of the Range: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows of the Range: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

End Sub

⧭ 코드 설명:

• 이 코드는 두 가지 입력을 받습니다. 범위의 첫 번째 셀(First_Cell)과 범위에 포함할 전체 행 수(Number_of_Rows)입니다.
• 그런 다음 First_Cell부터 지정한 행 수만큼 이어지는 범위 개체 Rng를 생성합니다.
• 예를 들어 First_Cell이 B4이고 Number_of_Rows가 10이면, 결과로 생성되는 Rng는 B4:B13입니다.
• First_Cell에는 항상 상대 참조(Relative Cell Reference)를 사용해야 합니다. 절대 참조나 혼합 참조는 사용하지 마세요. ($B$4나 $B4 대신 B4처럼 입력)

변수 행 번호로 VBA 범위 활용하는 4가지 예제

여기서는 Jupyter Group이라는 회사 직원들의 이름과 급여 데이터 세트를 사용합니다.

이제 이 데이터 세트를 대상으로 VBA를 활용해 변수 행 번호가 있는 범위를 다루는 방법을 살펴보겠습니다.

예제 1. VBA로 변수 행 번호의 범위 선택하기

먼저 VBA 매크로를 만들어 변수 행 번호가 있는 범위를 선택해 보겠습니다.

여기서는 첫 번째부터 다섯 번째 직원까지 총 5명의 이름을 선택해 보겠습니다.

아래 VBA 코드를 사용하면 됩니다.

⧭ VBA 코드:

Sub Select_Range()

First_Cell = InputBox("Enter the First Cell to Select: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Number of Rows to Select: ")

Rng = First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10)

Range(Rng).Select

End Sub

⧭ 실행 결과:

매크로(Select_Range)를 실행하면 두 개의 입력 상자가 나타납니다.

첫 번째 상자에서는 선택할 범위의 시작 셀을 입력하라고 요청합니다. 여기서는 첫 번째 직원이 있는 셀인 B4를 입력합니다.

확인(OK)을 클릭하면 두 번째 입력 상자가 나타나며, 선택할 행 수를 묻습니다. 여기서는 5를 입력합니다.

다시 확인(OK)을 클릭하면 데이터 세트에서 첫 5명 직원의 이름이 선택됩니다.

예제 2. 변수 행 번호의 범위에 숫자 입력하기

VBA를 사용하면 변수 행 번호가 있는 범위에 숫자를 입력할 수도 있습니다.

고정된 숫자뿐만 아니라 연속된 숫자 시리즈도 입력 가능합니다.

이번에는 첫 5명 직원에게 1부터 5까지 번호를 매겨 보겠습니다. 즉, 첫 5명 직원 이름 왼쪽 열(A4:A8)에 1~5의 숫자를 삽입하는 것입니다.

다음 VBA 코드를 사용하세요.

⧭ VBA 코드:

Sub Insert_Numbers()

First_Cell = InputBox("Enter the First Cell to Insert Number: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Insert Numbers: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Series_or_Fixed = Int(InputBox("Enter 1 to Enter a Series of Numbers: " + vbNewLine + vbNewLine + "OR" + vbNewLine + vbNewLine + "Enter 2 to Enter a Fixed Number: "))

If Series_or_Fixed = 1 Then

    First_Number = Int(InputBox("Enter the First Number: "))
    Increment = Int(InputBox("Enter the Increment: "))
    For i = 1 To Rng.Rows.Count
        Rng.Cells(i, 1) = First_Number + (i - 1) * Increment
    Next i

ElseIf Series_or_Fixed = 2 Then
    Number = Int(InputBox("Enter the Fixed Number: "))
    For i = 1 To Rng.Rows.Count
        Rng.Cells(i, 1) = Number
    Next i

End If

End Sub

⧭ 실행 결과:

매크로(Insert_Numbers)를 실행하면 여러 개의 입력 상자가 차례로 나타납니다.

첫 번째 상자에서는 숫자를 입력할 시작 셀을 요청합니다. 여기서는 A4를 입력합니다.

확인(OK)을 클릭하면 두 번째 상자가 나타나고, 숫자를 입력할 전체 행 수를 묻습니다. 여기서는 5를 입력합니다.

세 번째 상자에서는 숫자 시리즈를 넣을지 고정 숫자를 넣을지 선택합니다.

시리즈를 원하면 1, 고정 숫자를 원하면 2를 입력하세요.

여기서는 시리즈를 원하므로 1을 입력했습니다.

네 번째 상자에서는 시리즈의 시작 숫자를 입력합니다. 1~5 시리즈를 만들 것이므로 1을 입력했습니다.

마지막 상자에서는 증가값(increment)을 입력합니다. 1, 2, 3, 4, 5 시리즈라면 증가값은 1입니다.

확인(OK)을 클릭하면 A4:A8 범위에 1부터 5까지의 숫자가 입력됩니다.

함께 읽으면 좋은 글:

• Excel에서 열 번호 기준으로 VBA 범위 사용하기 (4가지 방법)
• 셀 값 기준으로 VBA 범위 선택하기 (7가지 방법)
• Excel에서 VBA Range 개체 활용하기 (5가지 속성)
• Excel에서 VBA로 범위 설정하기 (7가지 예제)
• VBA로 Excel 행 번호 찾기 (4가지 매크로)

예제 3. 변수 행 번호의 범위에 수학 연산 수행하기

다음으로, 변수 행 번호가 있는 범위에 수학 연산을 적용하는 매크로를 만들어 보겠습니다.

예를 들어 Jupyter Group의 사장이 첫 5명 직원의 급여를 각각 10,000달러씩 인상하고 싶어 한다고 가정해 봅시다.

이 목적을 달성하기 위한 매크로를 만들겠습니다.

아래 VBA 코드를 사용하세요.

⧭ VBA 코드:

Sub Mathematical_Operation()

First_Cell = InputBox("Enter the First Cell to Perform Operation: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Perform Operation: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Operation = Int(InputBox("Enter the Operation to Perform: " + vbNewLine + "Enter 1 for Addition: " + vbNewLine + "Enter 2 for Subtraction: " + vbNewLine + "Enter 3 for Multiplication: " + vbNewLine + "Enter 4 for Division: "))

Operations = Array("Add", "Subtract", "Multiply", "Divide")

Number = Int(InputBox("Enter the Number to " + Operations(Operation - 1) + ": "))

For i = 1 To Rng.Rows.Count

    If Operation = 1 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value + Number
    End If

    If Operation = 2 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value - Number
    End If

    If Operation = 3 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value * Number
    End If

    If Operation = 4 Then
        Rng.Cells(i, 1) = Rng.Cells(i, 1).Value / Number
    End If

Next i

End Sub

⧭ 실행 결과:

매크로(Mathematical_Operation)를 실행하면 4개의 입력 상자가 나타납니다.

첫 번째 상자에서는 평소와 같이 연산을 시작할 첫 번째 셀을 입력합니다. 이 예제에서는 첫 번째 직원의 급여가 있는 셀 C4입니다.

두 번째 상자에서는 연산을 적용할 전체 행 수를 입력합니다.

세 번째 상자에서는 수행할 연산 종류를 선택합니다.

• 덧셈은 1
• 뺄셈은 2
• 곱셈은 3
• 나눗셈은 4

여기서는 덧셈을 원하므로 1을 입력했습니다.

마지막으로 네 번째 상자에서는 더할 숫자를 입력합니다. 여기서는 10000을 입력했습니다.

확인(OK)을 클릭하면 첫 5명 직원의 급여가 각각 10,000달러씩 인상된 것을 확인할 수 있습니다.

예제 4. 변수 행 번호의 범위에 색상 적용하기

마지막으로, 변수 행 번호가 있는 범위의 셀에 색상을 입히는 매크로를 만들어 보겠습니다.

첫 5명 직원의 이름 셀에 노란색 배경을 적용해 보겠습니다.

다음 VBA 코드를 사용하세요.

⧭ VBA 코드:

Sub Color_Range()

First_Cell = InputBox("Enter the First Cell to Color: ")
Row_Number = Str(Range(First_Cell).Row)

Number_of_Rows = InputBox("Enter the Total Number of Rows to Color: ")

Set Rng = Range(First_Cell & ":" & Mid(First_Cell, 1, Len(First_Cell) - Len(Row_Number) + 1) & Mid(Str(Int(Number_of_Rows) + Int(Row_Number) - 1), 2, 10))

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

Background_or_Text = Int(InputBox("Enter 1 to Color the Whole Background of the Cells: " + vbNewLine + vbNewLine + "Or" + vbNewLine + vbNewLine + "Enter 2 to Color Only the Texts: "))

For i = 1 To Rng.Rows.Count

    If Background_or_Text = 1 Then
        Rng(i, 1).Interior.ColorIndex = Color_Code
    ElseIf Background_or_Text = 2 Then
        Rng.Cells(i, 1).Characters(1, Len(Rng.Cells(i, 1))).Font.ColorIndex = Color_Code
    End If

Next i

End Sub

⧭ 실행 결과:

매크로(Color_Range)를 실행하면 4개의 입력 상자가 나타납니다.

첫 번째 상자에서는 색상을 적용할 시작 셀을 입력합니다. 여기서는 첫 번째 직원이 있는 셀 B4입니다.

두 번째 상자에서는 색상을 적용할 전체 행 수를 입력합니다. 여기서는 5입니다.

세 번째 상자에서는 색상을 선택합니다. Microsoft 색상 코드(Color Index)에 따라 원하는 색상 번호를 입력하세요.

여기서는 노란색을 위해 6을 입력했습니다.

마지막 상자에서는 셀 전체 배경에 색을 입을지 텍스트에만 색을 입을지 선택합니다.

여기서는 1(전체 배경)을 입력했습니다.

확인(OK)을 클릭하면 첫 5명 직원의 이름 배경 전체가 노란색으로 강조됩니다.

마무리

지금까지 소개한 방법들을 활용하면 Excel에서 변수 행 번호가 있는 VBA 범위를 자유롭게 다룰 수 있습니다. 궁금한 점이 있다면 언제든지 문의해 주세요.

함께 읽으면 좋은 관련 글

• VBA Range Offset 사용법 (11가지 방법)
• Excel에서 범위 내 각 셀에 대한 VBA For Each 활용법 (3가지 방법)
• Excel VBA로 범위의 각 셀 순회하기 (완벽 가이드)
• Excel VBA로 다른 시트에 범위 복사하기 (가장 쉬운 8가지 방법)
• Excel VBA: 열에서 문자열 찾고 행 번호 반환하기
• Excel 수식에서 행 번호 증가시키기 (유용한 6가지 방법)