Computer >> Hướng Dẫn Máy Tính >  >> Phần Mềm >> Office

Thành thạo VBA: 4 ví dụ thực tế về phạm vi hàng động trong Excel

Trong bài này, tôi sẽ chỉ cách dùng VBA range với số hàng biến đổi trong Excel. Bạn sẽ học làm việc với VBA range để chọn phạm vi ô, chèn số vào phạm vi, thực hiện phép toán trên phạm vi, và tô màu phạm vi, tất cả với số hàng biến đổi.

Mã VBA thiết lập Range với số hàng biến đổi (Xem nhanh)

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

⧭ Giải thích mã:

  • Mã nhận 2 đầu vào: ô đầu của phạm vi (First_Cell) và tổng số hàng biến đổi (Number_of_Rows).
  • Tạo phạm vi hàng Rng từ First_Cell đến số hàng bạn muốn.
  • Ví dụ: First_Cell là B4 và Number_of_Rows là 10, thì Rng là B4:B13.
  • Luôn dùng Tham chiếu ô tương đối của First_Cell, không dùng Tuyệt đối hay Hỗn hợp (Dùng B4, không phải $B$4 hay $B4).

4 ví dụ dùng VBA Range với số hàng biến đổi trong Excel (Xem nhanh)

Có tập dữ liệu Tên và Lương nhân viên công ty Jupyter Group.

1. Chọn phạm vi với số hàng biến đổi bằng VBA

Phát triển Macro chọn phạm vi với số hàng biến đổi bằng VBA.

Chọn tên 5 nhân viên đầu tiên.

⧭ Mã 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

⧭ Kết quả:

Chạy Macro (Select_Range). Xuất hiện 2 hộp nhập liệu.

Hộp 1 hỏi ô đầu phạm vi muốn chọn. Ở đây là B4, ô nhân viên đầu.

Nhấn OK. Hộp 2 hỏi số hàng muốn chọn. Ở đây là 5.

Nhấn OK. Tên 5 nhân viên đầu được chọn.

Đọc thêm: Cách lấy số hàng từ Range với Excel VBA (9 ví dụ)

2. Chèn số vào phạm vi với số hàng biến đổi

Có thể chèn số vào phạm vi với số hàng biến đổi bằng VBA.

Có thể chèn số cố định hoặc dãy số.

Đánh số 5 nhân viên đầu từ 1 đến 5.

Tức chèn dãy 1 đến 5 bên trái tên 5 nhân viên đầu (A4:A8).

⧭ Mã 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

⧭ Kết quả:

Chạy Macro (Insert_Numbers). Xuất hiện vài hộp nhập.

Hộp 1 hỏi ô đầu chèn số. Ở đây A4.

Nhấn OK. Hộp 2 hỏi tổng số hàng chèn số. Ở đây 5.

Hộp 3 hỏi dãy số hay số cố định. Nhập 1 cho dãy, 2 cho cố định. Ở đây nhập 1.

Hộp 4 hỏi số đầu dãy. Muốn dãy 1 đến 5, nhập 1.

Hộp cuối hỏi bước nhảy. Dãy 1, 2, 3, 4, 5 bước nhảy 1.

Nhấn OK. Dãy 1 đến 5 xuất hiện ở A4:A8.

Đọc thêm: Cách dùng Range với số hàng biến đổi trong Excel

3. Thực hiện phép toán trên phạm vi với số hàng biến đổi

Phát triển Macro thực hiện phép toán trên phạm vi với số hàng biến đổi.

Ví dụ: Trưởng Jupyter Group muốn tăng lương 5 nhân viên đầu $10,000.

⧭ Mã 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
        If Operation = 2 Then Rng.Cells(i, 1) = Rng.Cells(i, 1).Value - Number
        If Operation = 3 Then Rng.Cells(i, 1) = Rng.Cells(i, 1).Value * Number
        If Operation = 4 Then Rng.Cells(i, 1) = Rng.Cells(i, 1).Value / Number
    Next i
End Sub

⧭ Kết quả:

Chạy Macro (Mathematical_Operation). Có 4 hộp nhập.

Hộp 1 hỏi ô đầu thực hiện phép toán. Ở đây lương nhân viên đầu, ô C4.

Hộp 2 hỏi tổng số hàng.

Hộp 3 hỏi phép toán: 1 Cộng, 2 Trừ, 3 Nhân, 4 Chia. Nhập 1.

Hộp 4 hỏi số để cộng. Nhập 10000.

Nhấn OK. Lương 5 nhân viên đầu tăng $10,000.

Đọc thêm: Tìm chuỗi trong cột trả về số hàng trong Excel (7 cách)

4. Tô màu ô của phạm vi với số hàng biến đổi

Phát triển Macro tô màu ô phạm vi với số hàng biến đổi.

Tô màu tên 5 nhân viên đầu đỏ.

⧭ Mã 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

⧭ Kết quả:

Chạy Macro (Color_Range). Có 4 hộp nhập.

Hộp 1 hỏi ô đầu tô màu. Ở đây nhân viên đầu, ô B4.

Hộp 2 hỏi tổng số hàng tô màu. Ở đây 5.

Hộp 3 chọn màu. Nhập chỉ số màu theo Mã màu Microsoft. Nhập 6 (Vàng).

Hộp cuối hỏi tô nền cả ô hay chỉ chữ. Nhập 1 (Cả nền).

Nhấn OK. Nền 5 tên đầu được tô vàng.

Đọc thêm: Cách lấy số hàng ô hiện tại trong Excel (4 cách nhanh)

Kết luận

Dùng các phương pháp này, bạn có thể dùng VBA range với số hàng biến đổi trong Excel. Có thắc mắc? Hãy hỏi chúng tôi.

Bài viết liên quan

  • Cách dùng VBA Range Offset (11 cách)
  • VBA cho từng ô trong Range trong Excel (3 phương pháp)
  • Lặp qua Range cho từng ô với Excel VBA (Hướng dẫn tối thượng)
  • Excel VBA Copy Range sang trang khác (8 cách dễ nhất)
  • Excel VBA: Tìm chuỗi trong cột trả về số hàng
  • Cách tăng số hàng trong công thức Excel (6 cách tiện lợi)