Khi Michael thực hiện thao tác “Tìm tất cả” trong Excel, chương trình sẽ hiển thị một cách hữu ích danh sách tất cả các ô chứa bất cứ thứ gì anh ta đang tìm kiếm.

Michael muốn sao chép danh sách địa chỉ ô đó vào một trang tính khác, vì vậy anh ấy tự hỏi liệu có cách nào để sao chép danh sách đó vào Clipboard để anh ấy có thể dán nó vào một trang tính hay không.

Có một số cách bạn có thể thực hiện nhiệm vụ này và hầu hết chúng đều liên quan đến việc sử dụng macro. Tuy nhiên, trước khi đến với các phương pháp tiếp cận dựa trên macro, chúng ta hãy xem cách bạn có thể truy cập địa chỉ bằng cách sử dụng các dải ô đã đặt tên và Trình quản lý tên:

  1. Sử dụng FindAll như trước đây, nhưng không đóng hộp thoại Tìm và Thay thế.

  2. Trong danh sách địa chỉ bạn được hiển thị, cuộn xuống dưới cùng, giữ phím Shift và nhấp vào kết quả phù hợp cuối cùng. Excel chọn tất cả các ô phù hợp.

  3. Nhấn Esc để đóng hộp thoại Tìm và Thay thế. Tất cả các ô phù hợp vẫn được chọn.

  4. Nhập tên vào Hộp Tên (ngay bên trái thanh Công thức và ngay trên ô A1). Điều này tạo ra một phạm vi được đặt tên bao gồm tất cả các ô đã chọn.

  5. Hiển thị tab Công thức của ruy-băng.

  6. Nhấp vào công cụ Name Manger. Excel sẽ hiển thị hộp thoại của Trình quản lý tên. (Xem Hình 1.)

  7. Nhấp vào tên bạn đã tạo ở bước 4.

  8. Danh sách các ô sẽ nằm trong hộp Tham chiếu đến, ở cuối hộp thoại.

Tại thời điểm này, bạn có thể sao chép thông tin trong hộp Tham khảo và dán nó vào bất kỳ thứ gì bạn muốn (bao gồm cả một trang tính khác). Bạn sẽ cần chỉnh sửa dữ liệu một chút sau khi dán, vì danh sách chỉ có vậy – một danh sách nối tiếp các địa chỉ ô.

Rõ ràng, điều này ảnh hưởng đến sổ làm việc của bạn, vì nó tạo ra một phạm vi được đặt tên. Nếu bạn làm điều đó nhiều lần, bạn sẽ có nhiều phạm vi được đặt tên được tạo.

Tất nhiên, điều này có thể nhanh chóng trở nên khó sử dụng nếu bạn cần thực hiện nhiệm vụ khá thường xuyên. Đây là lúc các giải pháp vĩ mô phát huy tác dụng. Sau đây là một ví dụ về macro sẽ tìm kiếm một giá trị cụ thể và sau đó đặt địa chỉ của mọi ô chứa giá trị đó vào một trang tính khác.

Sub CellAdressList()

Dim c1 As String     Dim nxt As String

Sheets("Sheet1").Select     Range("A1").Select     Cells.Find(What:="qrs", After:=ActiveCell, _       LookIn:=xlValues, LookAt:=xlWhole, _       SearchOrder:=xlByRows, SearchDirection:=xlNext, _       MatchCase:=False, SearchFormat:=False).Activate     c1 = ActiveCell.Address     Sheets("Sheet2").Select     Range("A1").Select     Range("A1").Value = c1     Do Until nxt = c1         Sheets("Sheet1").Select         Cells.FindNext(After:=ActiveCell).Activate         nxt = ActiveCell.Address         Sheets("Sheet2").Select         ActiveCell.Offset(1, 0).Select         ActiveCell.Value = nxt     Loop     ActiveCell.Value = ""

End Sub

Macro đưa ra một vài giả định. Đầu tiên, nó giả định rằng bạn đang tìm kiếm thông tin trên trang tính có tên là Sheet1. Thứ hai, nó giả sử bạn muốn danh sách các địa chỉ được đặt trong trang tính có tên là Sheet2. Cuối cùng, nó giả định rằng bạn đang tìm kiếm giá trị “qrs” trong Sheet1. Tất cả các yếu tố này của macro có thể được thay đổi, nếu muốn.

Đối với một cái gì đó linh hoạt hơn một chút, hãy xem xét macro sau. Nó giả định rằng bạn đã chọn tất cả các ô có chứa giá trị bạn muốn. (Nói cách khác, bạn cần thực hiện các bước từ 1 đến 3 trong số các bước gần phần đầu của mẹo này.) Sau đó, bạn có thể chạy macro.

Sub CopyFindAllSelection()

Dim outcell As Range     Dim c As Range

Set outcell = Range("Sheet2!A1")

For Each c In Selection         outcell.Value = c.Address         Set outcell = outcell.Offset(1, 0)

Next End Sub

Kết quả là địa chỉ của các ô đã chọn được đặt vào trang tính Sheet2. Macro này linh hoạt hơn một chút vì nó cho phép bạn tìm thấy bất kỳ thứ gì trong bất kỳ trang tính nào. Phần duy nhất “được mã hóa cứng”

là trang tính (Sheet2) mà các địa chỉ được đặt vào.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13581) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.