Giúp dùm mình viết code thay thế hàm vlookup và code update (1 người xem)

[phucuongn]

Chào các anh chị trên diễn đàn ! mình có một vấn đề nhờ các anh chị giúp mình
Mình có một file gồm 3 sheet (data, chitiet,tonghop)
mình muốn khi nhập dữ liệu ở sheet chitiet thì sheet này dò tìm ở sheet data để lấy dữ liệu qua
sau đó mình nhấn nút send thì các dữ liệu ở sheet chitiet sẽ gửi qua sheet tonghop để lưu lại hàng ngày
đồng thời xóa dữ liệu ở sheet chitiet
Nhờ các anh chị giúp mình viết code thay thế hàm vlookup và code update khi nhấn nút send
cám ơn các anh chị rất nhiều

 

[namkpac]

Code chạy đúng như mong muốn của bạn là tốt rồi.

Tôi không có dùng Y!M và nói chung là các phần mềm chat. Có vấn đề gì, chúng ta cùng trao đổi trên GPE luôn. Thực ra thì tôi cũng đang học, và tôi cũng muốn được học nhiều hơn từ các thành viên khác, chắc gì cách giải quyết của tôi đã hay?! Có thể các thành viên khác có nhiều cách giải quyết hay hơn. Vì vậy, nếu chúng ta trao đổi các vấn đề ở trên này thì cả tôi, bạn và nhiều thành viên khác cùng được học hỏi lẫn nhau. Vậy nhé!

Thân mến!

Em có một câu hỏi này muốn hỏi anh và mọi người trên diễn đàn giúp em, cũng file ở trên, giờ em muốn change vào sheet như trên với nội dung là các đoạn code đó nằm trong 1 thủ tục ko tách ra vì em thử đưa nhiều hàm if vào trong code mà cứ mỗi lần làm cho 1 cột thì lại khai báo lại, các điều kiện thì khác nhau, các biến và mảng khác nhau.

 

[namkpac]

Anh giúp em gom code lại thành 1 được ko ạ? em thử gom lại nhưng vẫn chưa được.

 

[nghiaphuc]

Em có một câu hỏi này muốn hỏi anh và mọi người trên diễn đàn giúp em, cũng file ở trên, giờ em muốn change vào sheet như trên với nội dung là các đoạn code đó nằm trong 1 thủ tục ko tách ra vì em thử đưa nhiều hàm if vào trong code mà cứ mỗi lần làm cho 1 cột thì lại khai báo lại, các điều kiện thì khác nhau, các biến và mảng khác nhau.

Bạn gỡ bớt 1 file đi chứ để chi cả 2 file dữ vậy?
Sau khi gom lại thành "1 cục" thì code còn lại thế này:
[GPECODE=vb]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cll As Range, Rng As Range
If Target.Column <> 6 And Target.Column <> 9 Then Exit Sub
For Each Cll In Intersect(Target, [F:F,I:I])
If Cll = "" Then
Cll.Offset(, IIf(Cll.Column = 6, -2, 1)).ClearContents
Else
If Cll.Column = 6 Then
Cll.Offset(, -2) = Cll.Offset(, 1) & Cll.Offset(, 2)
Else
Set Rng = Sheet2.[A:A].Find(Cll, LookAt:=xlWhole)
If Rng Is Nothing Then
Cll.Offset(, 1).ClearContents
Else
Cll.Offset(, 1) = Rng.Offset(, 2)
End If
End If
End If
Next
End Sub[/GPECODE]
À, khi bạn muốn khóa Sub Worksheet_Change của bạn lại thì chỉ đơn giản là bạn đổi tên nó thành Worksheet_Change1 (hoặc đại loại là thay đổi tên một chút xíu) thôi chứ không cần thêm dấu nháy đơn trước tất cả các dòng lệnh của nó đâu.

 

[namkpac]

Bạn gỡ bớt 1 file đi chứ để chi cả 2 file dữ vậy?
Sau khi gom lại thành "1 cục" thì code còn lại thế này:
[GPECODE=vb]Private Sub Worksheet_Change(ByVal Target As Range)
End Sub[/GPECODE]
À, khi bạn muốn khóa Sub Worksheet_Change của bạn lại thì chỉ đơn giản là bạn đổi tên nó thành Worksheet_Change1 (hoặc đại loại là thay đổi tên một chút xíu) thôi chứ không cần thêm dấu nháy đơn trước tất cả các dòng lệnh của nó đâu.

Dạ em cảm ơn anh chỉ dạy. Hay thật em ko hiểu nổi vba lại hay như vậy. Anh chỉ cho em với được ko? còn nếu như có những cột khác mà cũng có hàm if nhưng nằm trong 1 sheet này thì sao ạ? vì trong file em gửi có 2 cột A và B trong đó có 2 công thức if nhưng em ko biết lồng nó vào như thế nào anh chỉ giúp em được ko? anh giúp em phân tích nghĩa của từng dòng lệnh với? còn gỡ bớt file em gỡ đuợc rồi.

 

[namkpac]

Dạ em cảm ơn anh chỉ dạy. Hay thật em ko hiểu nổi vba lại hay như vậy. Anh chỉ cho em với được ko? còn nếu như có những cột khác mà cũng có hàm if nhưng nằm trong 1 sheet này thì sao ạ? vì trong file em gửi có 2 cột A và B trong đó có 2 công thức if nhưng em ko biết lồng nó vào như thế nào anh chỉ giúp em được ko? anh giúp em phân tích nghĩa của từng dòng lệnh với? còn gỡ bớt file em gỡ đuợc rồi.

Giúp em gom công thức nằm trong 4 cột vào 1 sub worksheets change với

 

[namkpac]

Bạn gỡ bớt 1 file đi chứ để chi cả 2 file dữ vậy?
À, khi bạn muốn khóa Sub Worksheet_Change của bạn lại thì chỉ đơn giản là bạn đổi tên nó thành Worksheet_Change1 (hoặc đại loại là thay đổi tên một chút xíu) thôi chứ không cần thêm dấu nháy đơn trước tất cả các dòng lệnh của nó đâu.

Anh nghiaphuc và mọi người giúp mình câu hỏi trên với, thanks mọi người

 

[donghung1512]

Em nhờ mọi người viết COde giúp em với

 

[Hoàng Nhật Phương]

Code thay cho hàm Vlookup ?

Xin chào các bạn,

Các bạn giúp đỡ Oanh Thơ code thay cho hàm Vlookup theo file đính kèm này với ạ.

 

[Ba Tê]

Xin chào các bạn,

Các bạn giúp đỡ Oanh Thơ code thay cho hàm Vlookup theo file đính kèm này với ạ.

Code này thay "Y chang" hàm Vlookup trong file của bạn nè, nếu muốn tổng quát hơn thì bạn tự chỉnh.

Public Sub GPE()
Dim sArr(), dArr(), I As Long, J As Long, R As Long
sArr = Sheets("Data").Range("C4:I18").Value
dArr = Sheets("Sheet1").Range("C5:C8").Resize(, 6).Value
For I = 1 To 4
    R = dArr(I, 1) + 1
    For J = 2 To 6
        dArr(I, J) = sArr(R, J)
    Next J
Next I
Sheets("Sheet1").Range("C5:C8").Resize(, 6) = dArr
End Sub

 

[Hoàng Nhật Phương]

Code này thay "Y chang" hàm Vlookup trong file của bạn nè, nếu muốn tổng quát hơn thì bạn tự chỉnh.

Public Sub GPE()
Dim sArr(), dArr(), I As Long, J As Long, R As Long
sArr = Sheets("Data").Range("C4:I18").Value
dArr = Sheets("Sheet1").Range("C5:C8").Resize(, 6).Value
For I = 1 To 4
    R = dArr(I, 1) + 1
    For J = 2 To 6
        dArr(I, J) = sArr(R, J)
    Next J
Next I
Sheets("Sheet1").Range("C5:C8").Resize(, 6) = dArr
End Sub

Cảm ơn bạn Ba Tê đã giúp đỡ.
Dữ liệu thực của Oanh Thơ rất nhiều dòng và số dòng trong "sheet1" luôn biến động không cố định bao nhiêu dòng.
Bạn có thể code giúp Oanh Thơ bằng phương pháp sử dụng Dictionary được không ?

 

[langtuchungtinh360]

Cảm ơn bạn Ba Tê đã giúp đỡ.
Dữ liệu thực của Oanh Thơ rất nhiều dòng và số dòng trong "sheet1" luôn biến động không cố định bao nhiêu dòng.
Bạn có thể code giúp Oanh Thơ bằng phương pháp sử dụng Dictionary được không ?

vì sao lại phải dùng Dictionary mới được vậy bạn. dữ liệu lớn thì Dic chưa chắc đã nhanh hơn mảng đâu, lý do muốn dùng Dictionary là gì (ngoài lọc duy nhất). đng tìm tòi tìm hiểu các thuật toán về vụ tìm kiếm, lọc thế này, được thì cứ đưa dữ liệu tràn lan đại hải lên. làm phát cho nhanh gọn đỡ phải lòi thêm phát sinh sau đó

 

[Ba Tê]

Cảm ơn bạn Ba Tê đã giúp đỡ.
Dữ liệu thực của Oanh Thơ rất nhiều dòng và số dòng trong "sheet1" luôn biến động không cố định bao nhiêu dòng.
Bạn có thể code giúp Oanh Thơ bằng phương pháp sử dụng Dictionary được không ?

- Nếu dữ liệu trong cột C (cột 1) của Data là các con số Thứ tự từ 1 đến ... mút chỉ thì đâu cần phải Dic.

Các bạn giúp đỡ Oanh Thơ code thay cho hàm Vlookup theo file đính kèm này với ạ.

- Code bên trên đã làm đúng yêu cầu: Code thay cho Vlookup() trong file của bạn.
- Nếu dữ liệu cột C (cột 1) của Data là các "ký tự lộn xộn" thì lại khác. Bạn thử code dưới.(Ví dụ của bạn phải tổng quát)
- Tên sheet nên cho nó 1 cái tên gì gì đó, chứ Sheet2 đặt tên là "Sheet1" thì "lãng" quá.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, R As Long, Col As Long, Rws As Long
With Sheets("Data")
    sArr = .Range("C5", .Range("C5").End(xlDown)).Resize(, 7).Value '7 = So cot cua Data'
End With
R = UBound(sArr)
With CreateObject("Scripting.Dictionary")
    For I = 1 To R
        .Add sArr(I, 1), I
    Next I
    With Sheets("Sheet1")
        tArr = .Range("C5", .Range("C5").End(xlDown)).Value
        R = UBound(tArr)
        Col = 5     '5 = So cot can lay du lieu cua Sheets(Sheet1)'
        ReDim dArr(1 To R, 1 To Col)
    End With
    For I = 1 To R
        If .Exists(tArr(I, 1)) Then
            Rws = .Item(tArr(I, 1))
            For J = 1 To Col
                dArr(I, J) = sArr(Rws, J + 1)
            Next J
        End If
    Next I
End With
Sheets("Sheet1").Range("D5").Resize(R, Col) = dArr
End Sub

 

[Hoàng Nhật Phương]

vì sao lại phải dùng Dictionary mới được vậy bạn. dữ liệu lớn thì Dic chưa chắc đã nhanh hơn mảng đâu, lý do muốn dùng Dictionary là gì (ngoài lọc duy nhất). đng tìm tòi tìm hiểu các thuật toán về vụ tìm kiếm, lọc thế này, được thì cứ đưa dữ liệu tràn lan đại hải lên. làm phát cho nhanh gọn đỡ phải lòi thêm phát sinh sau đó

Hi, cảm ơn bạn đã hỗ trợ ạ.
Oanh Thơ cũng không biết Dictionary dùng như thế nào nữa, vì lần trước Oanh Thơ có hỏi về vấn đề dùng code để thay thế cho hàm VLookup trong bài viết này, mình đã thấy bạn sử dụng Dictionary nên mới nghĩ vậy

https://www.giaiphapexcel.com/forum...hiều-sheet-theo-điều-kiện&p=764264#post764264

Nếu Dictionary đúng là chỉ lấy dữ liệu duy nhất thì không đúng với ý của Oanh Thơ rồi, vì trong cột C tại sheet1 của Oanh Thơ có rất nhiều dữ liệu trùng nhau.

 

[langtuchungtinh360]

Hi, cảm ơn bạn đã hỗ trợ ạ.
Oanh Thơ cũng không biết Dictionary dùng như thế nào nữa, vì lần trước Oanh Thơ có hỏi về vấn đề dùng code để thay thế cho hàm VLookup trong bài viết này, mình đã thấy bạn sử dụng Dictionary nên mới nghĩ vậy

https://www.giaiphapexcel.com/forum...hiều-sheet-theo-điều-kiện&p=764264#post764264

Nếu Dictionary đúng là chỉ lấy dữ liệu duy nhất thì không đúng với ý của Oanh Thơ rồi, vì trong cột C tại sheet1 của Oanh Thơ có rất nhiều dữ liệu trùng nhau.

vậy bạn có tìm hiểu và biết chút gì về vba chưa.
nếu biết chút rồi thì tôi gửi cho bạn công cụ hỗ trợ thêm ,tôi tự tham khảo và viết - không nhiều chức năng như các công cụ khác, hiện vẫn dùng nó để soạn code, do nhiều lệnh, hàm quá nên đành tạo công cụ trợ giúp ấy mà.

 

[Hoàng Nhật Phương]

- Nếu dữ liệu trong cột C (cột 1) của Data là các con số Thứ tự từ 1 đến ... mút chỉ thì đâu cần phải Dic.

- Code bên trên đã làm đúng yêu cầu: Code thay cho Vlookup() trong file của bạn.
- Nếu dữ liệu cột C (cột 1) của Data là các "ký tự lộn xộn" thì lại khác. Bạn thử code dưới.(Ví dụ của bạn phải tổng quát)
- Tên sheet nên cho nó 1 cái tên gì gì đó, chứ Sheet2 đặt tên là "Sheet1" thì "lãng" quá.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, R As Long, Col As Long, Rws As Long
With Sheets("Data")
    sArr = .Range("C5", .Range("C5").End(xlDown)).Resize(, 7).Value '7 = So cot cua Data'
End With
R = UBound(sArr)
With CreateObject("Scripting.Dictionary")
    For I = 1 To R
        .Add sArr(I, 1), I
    Next I
    With Sheets("Sheet1")
        tArr = .Range("C5", .Range("C5").End(xlDown)).Value
        R = UBound(tArr)
        Col = 5     '5 = So cot can lay du lieu cua Sheets(Sheet1)'
        ReDim dArr(1 To R, 1 To Col)
    End With
    For I = 1 To R
        If .Exists(tArr(I, 1)) Then
            Rws = .Item(tArr(I, 1))
            For J = 1 To Col
                dArr(I, J) = sArr(Rws, J + 1)
            Next J
        End If
    Next I
End With
Sheets("Sheet1").Range("D5").Resize(R, Col) = dArr
End Sub

Cảm ơn bạn Ba Tê rất nhiều Oanh Thơ sẽ dùng thử xem sao, nếu có vấn đề gì Oanh Thơ sẽ thông tin lại ạ.

 

[Hoàng Nhật Phương]

vậy bạn có tìm hiểu và biết chút gì về vba chưa.
nếu biết chút rồi thì tôi gửi cho bạn công cụ hỗ trợ thêm ,tôi tự tham khảo và viết - không nhiều chức năng như các công cụ khác, hiện vẫn dùng nó để soạn code, do nhiều lệnh, hàm quá nên đành tạo công cụ trợ giúp ấy mà.

Ôi thế thì tốt quá ạ,vậy bạn gửi cho Oanh Thơ để Oanh Thơ tìm hiểu nhé.

 

[Hoàng Nhật Phương]

- Nếu dữ liệu trong cột C (cột 1) của Data là các con số Thứ tự từ 1 đến ... mút chỉ thì đâu cần phải Dic.

- Code bên trên đã làm đúng yêu cầu: Code thay cho Vlookup() trong file của bạn.
- Nếu dữ liệu cột C (cột 1) của Data là các "ký tự lộn xộn" thì lại khác. Bạn thử code dưới.(Ví dụ của bạn phải tổng quát)
- Tên sheet nên cho nó 1 cái tên gì gì đó, chứ Sheet2 đặt tên là "Sheet1" thì "lãng" quá.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, R As Long, Col As Long, Rws As Long
With Sheets("Data")
    sArr = .Range("C5", .Range("C5").End(xlDown)).Resize(, 7).Value '7 = So cot cua Data'
End With
R = UBound(sArr)
With CreateObject("Scripting.Dictionary")
    For I = 1 To R
        .Add sArr(I, 1), I
    Next I
    With Sheets("Sheet1")
        tArr = .Range("C5", .Range("C5").End(xlDown)).Value
        R = UBound(tArr)
        Col = 5     '5 = So cot can lay du lieu cua Sheets(Sheet1)'
        ReDim dArr(1 To R, 1 To Col)
    End With
    For I = 1 To R
        If .Exists(tArr(I, 1)) Then
            Rws = .Item(tArr(I, 1))
            For J = 1 To Col
                dArr(I, J) = sArr(Rws, J + 1)
            Next J
        End If
    Next I
End With
Sheets("Sheet1").Range("D5").Resize(R, Col) = dArr
End Sub

Cảm ơn Ba Tê , code đúng ý Oanh Thơ rồi ạ! Có 1 vấn đề Oanh Thơ chữa rõ mong bạn chỉ thêm cho ạ.
Khi sử dụng công thức Oanh Thơ muốn lấy giá trị nằm trong code nào thì chỉ cần sửa lại chỉ số col_index , ví dụ nếu lấy giá trị từ cột 3 mà không lấy giá trị từ cột 2 thì chỉ cần sửa lại chỉ số 2 thành 3.
Vậy trong code của bạn nếu muốn sửa chỉ số như col_index thì sẽ sửa chỗ nào ạ.

 

[Ba Tê]

Cảm ơn Ba Tê , code đúng ý Oanh Thơ rồi ạ! Có 1 vấn đề Oanh Thơ chữa rõ mong bạn chỉ thêm cho ạ.
Khi sử dụng công thức Oanh Thơ muốn lấy giá trị nằm trong code nào thì chỉ cần sửa lại chỉ số col_index , ví dụ nếu lấy giá trị từ cột 3 mà không lấy giá trị từ cột 2 thì chỉ cần sửa lại chỉ số 2 thành 3.
Vậy trong code của bạn nếu muốn sửa chỉ số như col_index thì sẽ sửa chỗ nào ạ.

Hổng hiểu luôn!
Làm như vầy cho tổng quát luôn, dù hơi mất công một chút:
- Tạo 1 dòng phụ, ghi chỉ số cột cần lấy của bảng Data vào dòng này
- Code sẽ lấy đúng số đó, muốn thay đổi gì thì thay trong dòng phụ này, không đụng tới code.

 

[Hoàng Nhật Phương]

Hổng hiểu luôn!
Làm như vầy cho tổng quát luôn, dù hơi mất công một chút:
- Tạo 1 dòng phụ, ghi chỉ số cột cần lấy của bảng Data vào dòng này
- Code sẽ lấy đúng số đó, muốn thay đổi gì thì thay trong dòng phụ này, không đụng tới code.

Chào Bạn Ba Tê, đúng là cái rồi hihi, cảm ơn bạn nhiều!
Có 2 vấn đề nữa:
1.nếu dữ liệu trong Sheet"Data" không liên tục có dòng không có dữ liệu thì code chỉ lấy đến dòng cuối cùng trong mảng dữ liệu liền nhau mà không lấy dữ liệu đến dòng cuối cùng của bảng.
Ví dụ trong sheet"Data" Oanh Thơ xóa dữ liệu tại dòng 9 đi thì code chỉ tìm kiếm dữ liệu từ dòng 5 đến dòng 8 mà bỏ qua từ dòng 10 đến dòng 18.
2. thêm cột trong Sheet1 thì code bị lỗi

Làm phiền bạn nhiều quá ạ, thành thật xin lỗi vì Oanh Thơ không lường hết được các vấn đề

Bạn xem giúp vấn đề này với ạ.

 

[Ba Tê]

Chào Bạn Ba Tê, đúng là cái rồi hihi, cảm ơn bạn nhiều!
Có 1 vấn đề nữa nếu dữ liệu trong Sheet"Data" không liên tục có dòng không có dữ liệu thì code chỉ lấy đến dòng cuối cùng trong mảng dữ liệu liền nhau mà không lấy dữ liệu đến dòng cuối cùng của bảng.
Ví dụ trong sheet"Data" Oanh Thơ xóa dữ liệu tại dòng 9 đi thì code chỉ tìm kiếm dữ liệu từ dòng 5 đến dòng 8 mà bỏ qua từ dòng 10 đến dòng 18.
Bạn xem giúp vấn đề này với ạ.

Bạn tìm trong Sub dòng này:

sArr = .Range("C5", .Range("C5").End(xlDown)).Resize(, 7).Value '7 = So cot cua Data'

Thay bằng cái này:

sArr = .Range("C5", .Range("C1048576").End(xlUp)).Resize(, 7).Value    '7 = So cot cua Data'