Nhờ viết code xác định vùng nguồn tuỳ biến cho hàm Vloolup.

[TrungChinhs]

Xác định vùng nguồn tuỳ biến cho hàm Vlookup ?.

Trong một bảng tính có nhiều vùng, các vùng được định bởi 1 dòng (rỗng hoặc không rỗng tuỳ theo cột lựa chọn). Tôi muốn khi quét công thức Vlookup đến dòng của vùng nào thì vùng nguồn chỉ giới hạn trong số dòng vùng đó. Tôi đã dùng thử phương thức Special...để xác định vùng nhưng dòng của các vùng nguồn luôn là địa chỉ tương đối, Tôi không biết viết thế nào để cho nó trở thành địa chỉ tuyệt đối. Hiện Tôi vẫn đang tiếp tục mày mò nhưng khả năng thành công là không nhiều. Rất mong các bạn giúp đỡ.
ví dụ cụ thể trong file đính kèm.
Thank !

 

[TrungChinhs]

Tự động Xác định vùng nguồn cho hàm Vlookup.
Ví dụ ta có một danh sách gồm nhiều cửa hàng, mỗi cửa hàng có nhiều mặt hàng.
Nếu ta muốn tìm một số mặt hàng của cửa hàng nào đó bằng hàm Vlookup thì vùng tham chiếu chỉ được phép nằm trong phạm vi số dòng của cửa hàng đó (không được tràn sang dòng của cửa hàng khác), vùng nguồn tham chiếu của mỗi cửa hàng phải bằng tổng số dòng ghi các mặt hàng và phải là địa chỉ tuyệt đối.

Đoạn code này sẽ tự động xác định vùng nguồn cho hàm Vlookup khi bạn tìm kiếm tại một cửa hàng bất kỳ, mục tiêu chính ở đây là tự động xác định vùng nguồn cho hàm còn bạn muốn kết quả trả về là gì thì bạn sửa lại hàm theo yêu cầu của bạn (xem kết quả test thử công thức và kết quả ứng dụng trong file đính kèm)

Sub AutoNguonVlookup()
With Range("C5:C" & [F65536].End(xlUp).Row)
    For i = 1 To .SpecialCells(4).Areas.Count
         .SpecialCells(4).Areas(i).Offset(, 6).Resize(, 1).Select
         Selection.Value = "=VLOOKUP(RC5,R" & Selection.Row & _
               "C4:R" & .SpecialCells(4).Areas(i).Rows.Count + Selection.Row - 1 & "C4,1,0)"
                With Selection
'                    .Value = .Value
                End With
    Next
        End With
End Sub

Chúc thành công.

 

[TrungChinhs]

Code cho hàm Vlookup tự động tìm nguồn

Khi sử dụng hàm Vlookup thì việc xác định vị trí (địa chỉ dòng, địa chỉ cột) của bảng nguồn là rất quan trọng, theo cách thông thường chúng ta dùng bảng nguồn tĩnh (tức là vị trí bảng nguồn phải được xác định trước khi lập bảng đích và phải luôn cố định tại một vị trí nào đó trong bảng tính. Với một bảng đích có sẵn công thức khi muốn thay thế dữ liệu của bảng nguồn ta thường phải dán đè lên bảng nguồn cũ nhiều khi cũng bất tiện...

Để giúp cho việc sử dụng hàm Vlookup linh hoạt hơn tôi viết Sub hàm Vlookup tự động tìm nguồn, bạn có thể đặt bảng nguồn vào bất kỳ vị trí nào trong bảng tính, tức là bạn có thể chèn dòng, chèn cột hoặc xoá dòng, xoá cột hoặc di chuyển, copy dán sang chỗ khác tuỳ thích.

Trong File đính kèm tôi lập bảng nguồn và bảng đích trên cùng 1 sheet để dễ quan sát, nếu bạn muốn đặt bảng nguồn ở sheet khác thì phải chỉ định thêm tên sheet chứa bảng nguồn.
ví dụ

"=VLOOKUP(RC3,R" & i & "C" & j + 1 & ":R" & i + 200 & "C" & j + 10 & ",2,0)"

bạn sửa lại là

"=VLOOKUP(RC3,[COLOR=Red][B]Sheet2![/B][/COLOR]R" & i & "C" & j + 1 & ":R" & i + 200 & "C" & j + 10 & ",2,0)"

Rất mong được sự góp ý của các bạn để code được tối ưu hơn.

 

[sealand]

Với cách dùng hàm Vlookup như của bạn thì theo mình dùng name động có lẽ nhẹ nhàng hơn là dùng code thiết lập vùng dò tìm.

 

[TrungChinhs]

Bạn có thể cho vd cụ thể được không ? cái món name động này mình có xem qua trên GPE nhưng chưa hiểu lắm, rất cần một số vd để nghiên cứu.
Thân !

 

[SA_DQ]

Nếu phải đi tìm thì hãy dùng phương thức FIND() như sau đây

Sub TimSo1()
 Dim Rng As Range, sRng As Range
 
 Set Rng = ActiveSheet.UsedRange.Offset(, 3)
 Do
   Set sRng = Rng.Find(1, , xlFormulas, xlWhole)
   If sRng Is Nothing Then 
           Exit Sub
   Else
      Set Rng = Range(sRng, sRng.End(xlDown).End(xlToRight))
      MsgBox Rng.Address, , "GPE.COM"
 
      Exit Do
   End If
 Loop
End Sub

Tuy là dài, nhưng đừng chê nó nha; Nó nhanh gấp >250 lần 1 cái vòng lặp 500 lần của bạn đó!

Chúc vui vẻ!

 

[HideBoy]

Hb sau khi xem file excel thì thấy thế này:
Nếu bạn có 10 người trùng họ tên thì xác định chuyển đi và chuyển đến làm sao được?
Mỗi người thật sự chỉ có 3 trạng thái là chuyển đến và chuyển đi hoặc không gì. Nhưng tìm theo tên thì bạn bó tay với 10 hay 50 người trùng nhau.
Bạn quản lý hộ thế cũng không ổn. Không phân biệt được các hộ khác nhau.
Bạn lại dùng Vlookup hay VBA cũng phức tạp. timg kiếm cũng khó.

Mình sửa giúp bạn thế này: không cần dùng VBA, không lookup vùng, công thức sẵn có, dễ hiểu.
Chỉ cần nhập liệu. Chuyển một người đi bất cứ đâu cũng được (chỉ cần copy, paste)