Computer >> Máy Tính >  >> Phần mềm >> Office

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

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

Mã VBA để đặt một phạm vi với số hàng thay đổi trong Excel (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

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

⧭ Giải thích về mã:

  • Mã có hai đầu vào, ô đầu tiên của dải ô, được gọi là First_Cell và tổng số hàng biến của phạm vi, được gọi là Number_of_Rows .
  • Sau đó, nó tạo ra một loạt các hàng được gọi là Rng , bắt đầu từ First_Cell lên đến tổng số hàng bạn muốn.
  • Ví dụ:nếu First_Cell của bạn là B4 và Number_of_Rows là 10 , sau đó là đầu ra Rng là B4:B13 .
  • Luôn sử dụng Tham chiếu ô tương đối của First_Cell , không phải Tuyệt đối hoặc Tham chiếu ô hỗn hợp (Sử dụng B4 , không phải $ B $ 4 hoặc $ B4 ).

4 Ví dụ để Sử dụng Dải VBA với Số Hàng Biến đổi trong Excel (Xem nhanh)

Ở đây, chúng tôi đã có tập dữ liệu với Tên và Tiền lương của một số nhân viên của một công ty có tên là Jupyter Group.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Hôm nay, chúng tôi sẽ cố gắng làm việc với một phạm vi có số hàng thay đổi bằng cách sử dụng VBA trên tập dữ liệu này.

1. Chọn một phạm vi có số hàng thay đổi với VBA trong Excel

Trước hết, hãy phát triển Macro để chọn một dải ô có số hàng thay đổi bằng cách sử dụng VBA .

Hãy chọn tên của 5 đầu tiên nhân viên.

Bạn có thể sử dụng mã VBA sau cho mục đích này:

⧭ 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

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

⧭ Đầu ra:

Chạy Macro ( Select_Range ). Bạn sẽ nhận được hai hộp nhập liệu.

Ngày đầu tiên hộp sẽ yêu cầu bạn nhập ô đầu tiên của dải ô bạn muốn chọn. Đây là B4 , ô có nhân viên thứ nhất.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Nhấp vào OK . Hộp Đầu vào thứ 2 sẽ hỏi bạn số hàng bạn muốn chọn. Đây là 5 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Một lần nữa nhấp vào OK . Và bạn sẽ nhận được tên của 5 đầu tiên nhân viên được chọn trong tập dữ liệu của bạn.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Đọc thêm: Cách lấy số hàng từ phạm vi bằng Excel VBA (9 ví dụ)

2. Chèn số vào một phạm vi có số hàng thay đổi trong Excel

Bạn cũng có thể chèn số vào một dải ô có số hàng thay đổi bằng VBA trong Excel.

Bạn có thể chèn cả một số cố định và một chuỗi số.

Hãy đánh số thứ nhất 5 nhân viên từ 1 đến 5 .

Nghĩa là, chèn một chuỗi từ 1 đến 5 để lại cho tên của 5 đầu tiên nhân viên ( A4:A8 ).

Bạn có thể sử dụng mã VBA sau cho mục đích này:

⧭ 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

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

⧭ Đầu ra:

Chạy Macro này ( Chèn_số ). Bạn sẽ nhận được một vài hộp Nhập liệu.

Ngày đầu tiên hộp sẽ yêu cầu bạn nhập ô đầu tiên để chèn các số. Đây là A4 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Nhấp vào OK . Thứ 2 sẽ yêu cầu bạn nhập tổng số hàng để chèn số.

Đây là 5 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Thứ 3 hộp sẽ hỏi bạn xem bạn muốn một chuỗi số hay một số cố định.

Nhập 1 cho một chuỗi số.

Nhập 2 cho một số cố định.

Ở đây tôi muốn một dãy số. Vì vậy, tôi đã nhập 1 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Ngày 4 hộp sẽ hỏi bạn số đầu tiên của bộ truyện. Tôi muốn một loạt từ 1 đến 5 . Vì vậy, tôi đã nhập 1 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Ô cuối cùng sẽ yêu cầu bạn nhập số gia. Trong một loạt 1, 2, 3, 4, 5, đó là 1 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Nhấp vào OK . Và bạn sẽ nhận được một loạt từ 1 đến 5 trong phạm vi A4:A8 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Đọc thêm: Cách sử dụng phạm vi với số hàng biến trong Excel

Bài đọc tương tự

  • VBA để Sử dụng Phạm vi Dựa trên Số Cột trong Excel (4 Phương pháp)
  • Cách chọn phạm vi dựa trên giá trị ô VBA (7 cách)
  • Sử dụng Đối tượng Phạm vi của VBA trong Excel (5 Thuộc tính)
  • VBA để Đặt Phạm vi trong Excel (7 Ví dụ)
  • Cách Tìm Số Hàng Bằng VBA trong Excel (4 Macro)

3. Thực hiện phép toán trên một phạm vi có số hàng thay đổi trong Excel

Tiếp theo, chúng tôi sẽ phát triển Macro để thực hiện một phép toán trên một dải ô có số hàng thay đổi.

Ví dụ:hãy nghĩ trong giây lát rằng trưởng nhóm Jupyter muốn tăng lương của 5 nhân viên đầu tiên thêm 10.000 đô la .

Chúng tôi sẽ phát triển Macro để hoàn thành mục đích của mình.

Bạn có thể sử dụng mã VBA sau :

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

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

⧭ Đầu ra:

Chạy Macro ( Toán học_ Vận hành ). Bạn sẽ nhận được 4 Hộp nhập liệu.

Ngày đầu tiên , như thường lệ, sẽ yêu cầu bạn nhập ô đầu tiên để thực hiện thao tác.

Trong ví dụ này, đó là lương của nhân viên thứ nhất, ô C4 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Hộp thứ 2 sẽ hỏi bạn tổng số hàng để thực hiện thao tác.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Hộp thứ 3 sẽ yêu cầu bạn nhập thao tác bạn muốn thực hiện.

Nhập 1 để bổ sung.

Nhập 2 cho phép trừ.

3 cho phép nhân.

Và 4 cho một Bộ phận.

Ở đây tôi muốn một bổ sung. Vì vậy, tôi đã nhập 1 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Cuối cùng, ngày 4 hộp sẽ yêu cầu bạn nhập số để thêm. Ở đây, tôi đã nhập 10000 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Nhấp vào OK . Và bạn sẽ thấy lương của 5 nhân viên đầu tiên tăng thêm 10.000 đô la .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

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

4. Các ô màu của một phạm vi có số hàng thay đổi trong Excel

Cuối cùng, chúng tôi sẽ phát triển Macro để tô màu các ô của một dải ô với một số hàng thay đổi trong Excel.

Hãy tô màu đỏ cho tên của năm nhân viên đầu tiên.

Bạn có thể sử dụng mã VBA sau :

⧭ 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

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

⧭ Đầu ra:

Chạy Macro ( Color_Range ). Bạn sẽ nhận được 4 hộp nhập liệu.

Ngày đầu tiên hộp sẽ yêu cầu bạn nhập ô đầu tiên để tô màu.

Đây là nhân viên đầu tiên, ô B4.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Thứ 2 hộp sẽ yêu cầu tổng số hàng để tô màu. Đây là 5 .

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Thứ 3 hộp sẽ yêu cầu bạn chọn màu sắc. Nhập bất kỳ chỉ mục màu nào theo Mã màu của Microsoft .

Ở đây, tôi đã nhập 6 (Đối với màu vàng màu sắc).

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Ô cuối cùng sẽ hỏi bạn muốn tô màu toàn bộ nền của các ô hay chỉ các văn bản.

Tôi đã nhập 1 (Toàn bộ nền).

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

Nhấp vào OK . Và bạn sẽ nhận được toàn bộ nền của 5 tên đầu tiên được đánh dấu bằng màu vàng.

Dải VBA với số hàng biến trong Excel (4 Ví dụ)

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

Kết luận

Sử dụng các phương pháp này, bạn có thể sử dụng VBA phạm vi với một số hàng thay đổi trong Excel. Bạn có câu hỏi nào không? Hãy hỏi chúng tôi.

Các bài viết liên quan

  • Cách Sử dụng Chênh lệch Phạm vi VBA (11 Cách)
  • VBA cho mỗi ô trong phạm vi trong Excel (3 phương pháp)
  • Lặp qua một phạm vi cho mỗi ô với Excel VBA (Hướng dẫn cơ bản)
  • Phạm vi sao chép VBA trong Excel sang Trang tính khác (8 cách dễ nhất)
  • Excel VBA:Tìm chuỗi trong cột và trả về số hàng
  • Cách Tăng Số Hàng trong Công thức Excel (6 Cách Tiện dụng)