Tìm và xóa vùng dữ liệu từ dưới lên

[hoahuongduong1986]

Dear Anh Chị và các bạn,
Em dùng Code dưới để VLOOKup từ Sheet 6 sang ba Cột của SHeet 4 với giới hạn tối đa là 30 ngàn dòng. Nhưng thực chất số lượng dòng nhỏ hơn 30000 dòng. Viết đoạn Code như thế nào để sau khi lọc xong công đoạn này, Nó rò từ địa chỉ ô A30000 lên trên, gặp dữ liệu đầu tiên tại tọa độ Ax thì nó sẽ xóa toàn bộ từ vung (Ax: GH30000). Với x là tọa độ mà tại đó là giá trị đầu tiên từ A30000 rà soát lên.


Option Explicit
Sub VLookupL()
Dim i As Long, KQ(), Ma(), SoGiaoDich(), Item, Dic As Object
With Sheet6
Ma = Range(.[B2], .[B400].End(3)).Resize(, 4)
End With

With Sheet4
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3))

ReDim KQ(1 To UBound(SoGiaoDich), 1 To 3)
Set Dic = CreateObject("Scripting.dictionary")
For i = 1 To UBound(Ma)
Item = CStr(Ma(i, 1))
If Not Dic.exists(Item) Then
Dic.Add CStr(Ma(i, 1)), i
End If
Next i
For i = 1 To UBound(SoGiaoDich)
Item = CStr(SoGiaoDich(i, 1))
If Dic.exists(Item) Then
KQ(i, 1) = Ma(Dic.Item(Item), 2)
KQ(i, 2) = Ma(Dic.Item(Item), 3)
KQ(i, 3) = Ma(Dic.Item(Item), 4)
Else
KQ(i, 1) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 2) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 3) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
End If
Next i
.[DK2:N30000].ClearContents
.[DK2].Resize(i - 1, 3) = KQ
Set Dic = Nothing
End With

End Sub

 

[leonguyenz]

Bạn gửi file lên sẽ rõ hơn là đọc hết các dòng code bạn viết.

 

[HeSanbi]

Đoán sơ sơ, có lẽ là cột A số hàng chứa dữ liệu ít hơn các cột còn lại nên mới cần tìm dòng cuối chứa dữ liệu của cột A
sau đó sẽ xóa dữ liệu các cột khác bắt đầu từ sau dòng cuối này.

Bạn thử đoạn code ngắn này xem:

Sheet4.Range("A" & Sheet4..Range("A" & Rows.Count).End(xlUp).Row + 1 & ":GH30000").ClearContents

 

[SA_DQ]

Mình thử diễn dịch từ ngôn ngữ VBA của bạn sang tiếng Việt như sau & sai chổ nào bạn nói giúp nha:
B1: Lấy dữ liệu của cột [B:E] ở trang tính Sheet6 cho vô biến mảng Ma() đã khai báo
B2: Cũng lấy dữ liệu cột [CO] của trang Sheet4 cho vô biến mảng SoGiaoDich đã khai báo
B3: Khai báo thêm 1 biến mảng có tên là KQ với 3 cột & có số dòng bằng với số dòng trong mảng SoGiaoDich
B4: Lập danh sách duy nhất cho mảng số liệu chứa trong Ma() nhờ Dictionary (Dict)
B5: Tiến hành duyệt từ đầu chí cuối dữ liệu chứa trong SoGiaoDich()
B5.1: (Trong quá trình duyệt) lấy số liệu cột đầu của mảng, chuyển sang dạng chuỗi & gán vô tham biến Item (đã khai báo)
B5.2 Nếu chuỗi này có trong Dict thì tiến hành ghi các thành tố tương ứng của mảng Ma() sang mảng KQ()
B5.2 Bằng ngược lại thì ghi vô dòng tương ứng của KQ() là cụm từ hình như là "Cần Nhập Mã"
B6: Xóa dữ liệu cũa trên 3 cột bắt đầu từ cột 'DK'
Ghi lên 3 cột vừa xóa dữ liệu từ mảng KQ

còn đoạn này thì chưa thể hiểu:

Viết đoạn Code như thế nào để sau khi lọc xong công đoạn này, Nó rò từ địa chỉ ô A30000 lên trên, gặp dữ liệu đầu tiên tại tọa độ Ax thì nó sẽ xóa toàn bộ từ vung (Ax: GH30000). Với x là tọa độ mà tại đó là giá trị đầu tiên từ A30000 rà soát lên.

 

[hoahuongduong1986]

Đoán sơ sơ, có lẽ là cột A số hàng chứa dữ liệu ít hơn các cột còn lại nên mới cần tìm dòng cuối chứa dữ liệu của cột A
sau đó sẽ xóa dữ liệu các cột khác bắt đầu từ sau dòng cuối này.

Bạn thử đoạn code ngắn này xem:

Sheet4.Range("A" & Sheet4..Range("A" & Rows.Count).End(xlUp).Row + 1 & ":GH30000").ClearContents

Dear Anh, Anh nói đúng ý rồi ạ. Nhưng Code chưa đúng lắm ạ. Em đưa File ví dụ và diễn giải như sau ạ.
(1) Mục đích của em là em đặt Mảng dữ liệu A1:16 => Insert => Table => Để mục đích là đặt Table cho bảng phục vụ cho Pivotable tự động => Chắc anh đã hiểu ý em.
(2) Em hiểu rằng sau khi Chạy code Vlookup trên theo điều kiện tìm là cột A chẳng hạn nhưng em đã cố định là xuống 30000 dòng (File mẫu là 28 dòng) thì mặc dù không có dữ liệu nhưng nó vẫn hiểu là vùng và hiểu đó là Table mới. Nên em diễn giải lại mong muốn như sau.

=> Nó dò lên từ ô cố định A28 đi lên (Ở Code là A30000), gặp một ô nào có Len(Ax)>3 (Ở bảng là ô A16 ) thì nó sẽ xóa toàn bộ từ A16:E28. Vì sao em nghĩ mình cần gắn thêm điều kiện cho Len(A16)>3 vì em nghĩ dù không có số liệu nhưng khi chạy code trên thì nó vẫn hiểu rằng vùng A16:A28 vẫn có dữ liệu mặc dù nó là rỗng

Em hết ý ạ. Hi vọng các anh hiểu ạ !

 

[LamNA]

Dear Anh, Anh nói đúng ý rồi ạ. Nhưng Code chưa đúng lắm ạ. Em đưa File ví dụ và diễn giải như sau ạ.
(1) Mục đích của em là em đặt Mảng dữ liệu A1:16 => Insert => Table => Để mục đích là đặt Table cho bảng phục vụ cho Pivotable tự động => Chắc anh đã hiểu ý em.
(2) Em hiểu rằng sau khi Chạy code Vlookup trên theo điều kiện tìm là cột A chẳng hạn nhưng em đã cố định là xuống 30000 dòng (File mẫu là 28 dòng) thì mặc dù không có dữ liệu nhưng nó vẫn hiểu là vùng và hiểu đó là Table mới. Nên em diễn giải lại mong muốn như sau.

=> Nó dò lên từ ô cố định A28 đi lên (Ở Code là A30000), gặp một ô nào có Len(Ax)>3 (Ở bảng là ô A16 ) thì nó sẽ xóa toàn bộ từ A16:E28. Vì sao em nghĩ mình cần gắn thêm điều kiện cho Len(A16)>3 vì em nghĩ dù không có số liệu nhưng khi chạy code trên thì nó vẫn hiểu rằng vùng A16:A28 vẫn có dữ liệu mặc dù nó là rỗng

Em hết ý ạ. Hi vọng các anh hiểu ạ !

Thử trước khi chạy code thì resize lại bảng table

Option Explicit
Sub VLookupL()
Dim i As Long, KQ(), Ma(), SoGiaoDich(), Item, Dic As Object
'Khai bao them bien
Dim tbl As ListObject
With Sheet6
Ma = Range(.[B2], .[B400].End(3)).Resize(, 4)
End With

With Sheet4
'--------------------------------------------
' Xoa o rong trong table
Set tbl = .ListObjects("Table1") 'thay doi ten table theo file cua bạn
    tbl.Resize tbl.Range.CurrentRegion
'--------------------------
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3))
ReDim KQ(1 To UBound(SoGiaoDich), 1 To 3)
Set Dic = CreateObject("Scripting.dictionary")
For i = 1 To UBound(Ma)
Item = CStr(Ma(i, 1))
If Not Dic.exists(Item) Then
Dic.Add CStr(Ma(i, 1)), i
End If
Next i
For i = 1 To UBound(SoGiaoDich)
Item = CStr(SoGiaoDich(i, 1))
If Dic.exists(Item) Then
KQ(i, 1) = Ma(Dic.Item(Item), 2)
KQ(i, 2) = Ma(Dic.Item(Item), 3)
KQ(i, 3) = Ma(Dic.Item(Item), 4)
Else
KQ(i, 1) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 2) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 3) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
End If
Next i
[DK2:N30000].ClearContents
[DK2].Resize(i - 1, 3) = KQ
Set Dic = Nothing
End With
End Sub

 

[hoahuongduong1986]

Thử trước khi chạy code thì resize lại bảng table

Option Explicit
Sub VLookupL()
Dim i As Long, KQ(), Ma(), SoGiaoDich(), Item, Dic As Object
'Khai bao them bien
Dim tbl As ListObject
With Sheet6
Ma = Range(.[B2], .[B400].End(3)).Resize(, 4)
End With

With Sheet4
'--------------------------------------------
' Xoa o rong trong table
Set tbl = .ListObjects("Table1") 'thay doi ten table theo file cua bạn
    tbl.Resize tbl.Range.CurrentRegion
'--------------------------
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3))
ReDim KQ(1 To UBound(SoGiaoDich), 1 To 3)
Set Dic = CreateObject("Scripting.dictionary")
For i = 1 To UBound(Ma)
Item = CStr(Ma(i, 1))
If Not Dic.exists(Item) Then
Dic.Add CStr(Ma(i, 1)), i
End If
Next i
For i = 1 To UBound(SoGiaoDich)
Item = CStr(SoGiaoDich(i, 1))
If Dic.exists(Item) Then
KQ(i, 1) = Ma(Dic.Item(Item), 2)
KQ(i, 2) = Ma(Dic.Item(Item), 3)
KQ(i, 3) = Ma(Dic.Item(Item), 4)
Else
KQ(i, 1) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 2) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
KQ(i, 3) = "C" & ChrW(7847) & "n nh" & ChrW(7853) & "p mã"
End If
Next i
[DK2:N30000].ClearContents
[DK2].Resize(i - 1, 3) = KQ
Set Dic = Nothing
End With
End Sub

Anh ơi,
ý của anh em thấy rất hay ạ. Nhưng mà thực chất em nghĩ tại đoạn code dưới nó hiểu dù có Vlookup có ra hay không thì nó vẫn hiểu rằng Table cố định là đến dòng 30000.
With Sheet4
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3))

Do đó em thấy, sẽ có hai cách ạ:
(Cách 1) Sửa lại đoạn
With Sheet4
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3)) => KHông cố định CO30000 nữa mà tại cột CO sẽ thay bằng tìm từ dưới lên gặp COx thì Vlookup cho điều kiện tìm tại VÙng CO2:COx. Còn từ COx đền dòng tận cùng của Excel thì không thực hiện (COx là điểm đầu tiên từ dưới lên khác rỗng, hoặc ăn chắc cho Len(COx)>3 ạ )=> Em thấy cách này là hay và nhẹ
(Cách 2)

EM thấy đoạn
Sheet4.Range("A" & Sheet4..Range("A" & Rows.Count).End(xlUp).Row + 1 & ":GH30000").ClearContent nó sắp ổn rồi ạ. Chỉ là thiết lập lại tìm từ A30000 lên Ax với Len(Ax)>3 thì xóa ngang toàn bộ vùng từ Ax:A30000 sang phải cho hết cột Excel ạ.

 

[LamNA]

Anh ơi,
ý của anh em thấy rất hay ạ. Nhưng mà thực chất em nghĩ tại đoạn code dưới nó hiểu dù có Vlookup có ra hay không thì nó vẫn hiểu rằng Table cố định là đến dòng 30000. Do đó em nghĩ chỉ còn cách sau khi lọc xong, thì xóa toàn bộ vùng rỗng thì mới làm được ạ.
With Sheet4
SoGiaoDich = .Range(.[CO2], .[CO30000].End(3))

EM thấy đoạn
Sheet4.Range("A" & Sheet4..Range("A" & Rows.Count).End(xlUp).Row + 1 & ":GH30000").ClearContent nó sắp ổn rồi ạ. Chỉ là thiết lập lại tìm từ A30000 lên Ax với Len(Ax)>3 thì xóa ngang toàn bộ vùng từ Ax:A30000 sang phải cho hết cột Excel ạ.

Nói thiệt đọc hết các bài bạn viết mình không hiểu gì cả, hãy gửi cái file của bạn lên đi để dễ hình dung, còn file mẫu bài 5 với diễn giải thấy không liên quan nên rất khó hiểu

 

[hoahuongduong1986]

Nói thiệt đọc hết các bài bạn viết mình không hiểu gì cả, hãy gửi cái file của bạn lên đi để dễ hình dung, còn file mẫu bài 5 với diễn giải thấy không liên quan nên rất khó hiểu

Đây ạ,
Ví dụ SHeet SO giao dich em đặt tên Table có dữ liệu là Saoke. Và Pivot sang sheet Pivot. Khi Thêm số liệu tiếp vào hoặc copy tiếp vào. thì vùng của nó lấy xuống tận 650000 dòng thì nó sẽ luôn tồn tại Blank ạ.

Cái đoạn SoGiaoDich = .Range(.[B4], .[B65000].End(3)) mình sẽ không cố định tìm tới 65k dòng thì mình thay bằng Rò từ Bx đến B4 (Với ô Bx là ô đầu tiên từ dưới cùng đi lên mà nó khác rỗng hoặc cho ăn chắc cho Len(Bx) >3 ạ. Và thực hiện Vlookup từ B4:Bx thôi không phải xuống tận dòng 65K ạ. và Table nó chỉ trong vùng từ B4:Hx ạ

 

[LamNA]

Nói thiệt đọc hết các bài bạn viết mình không hiểu gì cả, hãy gửi cái file của bạn lên đi để dễ hình dung, còn file mẫu bài 5 với diễn giải thấy không liên quan nên rất khó hiểu

Đừng có nói là ý muốn vlookup lần 1 được 28 dòng dữ liệu, chạy lần 2 không muốn xóa 28 dòng mà lệnh xóa bắt đầu từ dòng 29 nhe

Bài đã được tự động gộp: 30/4/19

Đây ạ,
Ví dụ SHeet SO giao dich em đặt tên Table có dữ liệu là Saoke. Và Pivot sang sheet Pivot. Khi Thêm số liệu tiếp vào hoặc copy tiếp vào. thì vùng của nó lấy xuống tận 650000 dòng thì nó sẽ luôn tồn tại Blank ạ.

Cái đoạn SoGiaoDich = .Range(.[B4], .[B65000].End(3)) mình sẽ không cố định tìm tới 65k dòng thì mình thay bằng Rò từ Bx đến B4 (Với ô Bx là ô đầu tiên từ dưới cùng đi lên mà nó khác rỗng hoặc cho ăn chắc cho Len(Bx) >3 ạ. Và thực hiện Vlookup từ B4:Bx thôi không phải xuống tận dòng 65K ạ. và Table nó chỉ trong vùng từ B4:Hx ạ

Đang không trên pc chút xem file sẽ hỗ trợ nhé

 

[hoahuongduong1986]

Đừng có nói là ý muốn vlookup lần 1 được 28 dòng dữ liệu, chạy lần 2 không muốn xóa 28 dòng mà lệnh xóa bắt đầu từ dòng 29 nhe

Bài đã được tự động gộp: 30/4/19

Đang không trên pc chút xem file sẽ hỗ trợ nhé

Không ạ ! )))

Ví dụ SHeet SO giao dich em đặt tên Table có dữ liệu là Saoke. Và Pivot sang sheet Pivot. Khi Thêm số liệu tiếp vào hoặc copy tiếp vào. thì vùng của nó lấy xuống tận 650000 dòng thì nó sẽ luôn tồn tại Blank ạ.

Cái đoạn SoGiaoDich = .Range(.[B4], .[B65000].End(3)) mình sẽ không cố định tìm tới 65k dòng thì mình thay bằng Rò từ Bx đến B4 (Với ô Bx là ô đầu tiên từ dưới cùng đi lên mà nó khác rỗng hoặc cho ăn chắc cho Len(Bx) >3 ạ. Và thực hiện Vlookup từ B4:Bx thôi không phải xuống tận dòng 65K ạ. và Table nó chỉ trong vùng từ B4:Hx ạ

 

[LamNA]

Đây ạ,
Ví dụ SHeet SO giao dich em đặt tên Table có dữ liệu là Saoke. Và Pivot sang sheet Pivot. Khi Thêm số liệu tiếp vào hoặc copy tiếp vào. thì vùng của nó lấy xuống tận 650000 dòng thì nó sẽ luôn tồn tại Blank ạ.

Cái đoạn SoGiaoDich = .Range(.[B4], .[B65000].End(3)) mình sẽ không cố định tìm tới 65k dòng thì mình thay bằng Rò từ Bx đến B4 (Với ô Bx là ô đầu tiên từ dưới cùng đi lên mà nó khác rỗng hoặc cho ăn chắc cho Len(Bx) >3 ạ. Và thực hiện Vlookup từ B4:Bx thôi không phải xuống tận dòng 65K ạ. và Table nó chỉ trong vùng từ B4:Hx ạ

Bạn thêm dữ liệu ở đâu mà nó chạy xuống tận 650000 gửi mình xem

 

[hoahuongduong1986]

Bạn thêm dữ liệu ở đâu mà nó chạy xuống tận 650000 gửi mình xem

Anh ơi,
Em thử lại rồi, Code hum trước của anh nó chưa đúng anh ạ. Đây nhé, Em đã đặt vùng có dữ liệu B3:H11 là Table có tên là Saoke. Em thêm bất kỳ dữ liệu nào vào vùng C và E Nó đều hiện ra kết quả tại các cột F, G, H là "Cần nhập mã" trong khi đó điều kiện tìm của Hàm Vlookup là Cột B còn chưa nhập giá trị. Do đó khi pivot nó cứ ra blank và đáng nhẽ vùng tù B12:H12 trở xuống phải là trống rỗng ạ.

 

[be_09]

Anh ơi,
Em thử lại rồi, Code hum trước của anh nó chưa đúng anh ạ. Đây nhé, Em đã đặt vùng có dữ liệu B3:H11 là Table có tên là Saoke. Em thêm bất kỳ dữ liệu nào vào vùng C và E Nó đều hiện ra kết quả tại các cột F, G, H là "Cần nhập mã" trong khi đó điều kiện tìm của Hàm Vlookup là Cột B còn chưa nhập giá trị. Do đó khi pivot nó cứ ra blank và đáng nhẽ vùng tù B12:H12 trở xuống phải là trống rỗng ạ.

Bạn muốn làm cái gì thì nêu rỏ, chứ nội dung hiện tại là khác với tiêu đề. Với nội dung này thì tôi hiểu là:
1/ Khi nhập mã hàng cột B thì tự động gán dữ liệu cột F: H.
2/ Khi tạo PivotTable thì chỉ vùng có dữ liệu và bỏ qua dòng trống.
3/ Tôi thắc mắc: Nếu nói về bán hàng thì cần có phiếu hoặc hóa đơn bán hàng, nhưng trong bài viết thì không thấy đề cập đến?

 

[hoahuongduong1986]

Bạn muốn làm cái gì thì nêu rỏ, chứ nội dung hiện tại là khác với tiêu đề. Với nội dung này thì tôi hiểu là:
1/ Khi nhập mã hàng cột B thì tự động gán dữ liệu cột F: H.
2/ Khi tạo PivotTable thì chỉ vùng có dữ liệu và bỏ qua dòng trống.
3/ Tôi thắc mắc: Nếu nói về bán hàng thì cần có phiếu hoặc hóa đơn bán hàng, nhưng trong bài viết thì không thấy đề cập đến?

Dear Anh,
Yêu cầu đúng như anh nói ạ. Em muốn làm báo cáo tự động bằng Pivot - Cứ thay đổi dữ liệu tại vùng B:E thì
+ Trước tiên là tìm theo mã hàng tại cột B tham chiếu sang Sheet Ma => Kêt quả cho ra từ cột F, G, H
+ Em đặt tên cho vùng B3:H11 là table có tên Saoke để đảm bảo khi thay đổi dữ liệu thì nó vẫn luôn hiểu là vùng Saoke để Pivot lúc nào cũng chỉ trọn trong vùng cố số liệu. Tuy nhiên, Code Anh LamNA làm giúp em hiện bị lỗi là dù em không có mã hàng tại cột B nhưng có số liệu tại các cột khác là C,D,E thì nó vẫn ra chữ "Cần nhập mã" là sai vì bản chất nó phải Vlookup theo điều kiện mã tại cột B. Điều sai này dẫn tới Pivot luôn có blank ạ.

Còn về số liệu mã hàng phiếu nhập anh nói, thì để đơn giản em chỉ lấy ví dụ phù hợp với hiện tại của em thôi ạ. Anh xem giúp code của anh LamNA còn thiếu gì mà vẫn bị sai trường hợp này ạ. E cảm ơn anh ạ !

 

[batman1]

Anh ơi,
Em thử lại rồi, Code hum trước của anh nó chưa đúng anh ạ. Đây nhé, Em đã đặt vùng có dữ liệu B3:H11 là Table có tên là Saoke. Em thêm bất kỳ dữ liệu nào vào vùng C và E Nó đều hiện ra kết quả tại các cột F, G, H là "Cần nhập mã" trong khi đó điều kiện tìm của Hàm Vlookup là Cột B còn chưa nhập giá trị. Do đó khi pivot nó cứ ra blank và đáng nhẽ vùng tù B12:H12 trở xuống phải là trống rỗng ạ.

Bạn có

SoGiaoDich = .Range(.[B4], .[B65000].End(3))

Tôi thì luôn viết tường minh

SoGiaoDich = .Range(.[B4], .[B65000].End(xlUp))

Nếu bạn có B4:H16 là bảng thì .[B65000].End(xlUp).Row luôn trả về giá trị 16 (.[B65000].End(xlUp) trả về B16) cho dù B12:B16 không có dữ liệu (trống). Vì thế kết quả sai.

Phải nhớ đến ngày tận thế là nếu cột "hichic" thuộc bảng thì End(...) sẽ trả về dòng cuối cùng của bảng cho dù trên nó tất cả các ô đều trống.

Có những trường hợp bạn phải đi từ dưới lên, vd. bắt đầu từ dòng 30000, và xác định dòng đầu tiên gặp có dữ liệu. Đấy là dòng cuối cùng có dữ liệu. Vd. kiểm tra trong vòng Do While ... Loop

Nếu từ B4 trở xuống có ít nhất 2 ô có dữ liệu và không có ô nào trống ở giữa, tức không thể là B4:B7 và B9 có dữ liệu còn B8 trống, nếu thế thì có thể dùng

SoGiaoDich = .Range(.[B4], .[B4].End(xlDown))

 

[LamNA]

Dear Anh,
Yêu cầu đúng như anh nói ạ. Em muốn làm báo cáo tự động bằng Pivot - Cứ thay đổi dữ liệu tại vùng B:E thì
+ Trước tiên là tìm theo mã hàng tại cột B tham chiếu sang Sheet Ma => Kêt quả cho ra từ cột F, G, H
+ Em đặt tên cho vùng B3:H11 là table có tên Saoke để đảm bảo khi thay đổi dữ liệu thì nó vẫn luôn hiểu là vùng Saoke để Pivot lúc nào cũng chỉ trọn trong vùng cố số liệu. Tuy nhiên, Code Anh LamNA làm giúp em hiện bị lỗi là dù em không có mã hàng tại cột B nhưng có số liệu tại các cột khác là C,D,E thì nó vẫn ra chữ "Cần nhập mã" là sai vì bản chất nó phải Vlookup theo điều kiện mã tại cột B. Điều sai này dẫn tới Pivot luôn có blank ạ.

Còn về số liệu mã hàng phiếu nhập anh nói, thì để đơn giản em chỉ lấy ví dụ phù hợp với hiện tại của em thôi ạ. Anh xem giúp code của anh LamNA còn thiếu gì mà vẫn bị sai trường hợp này ạ. E cảm ơn anh ạ !

Mình thắc mắc bạn muốn code dựa vào mã hàng cột B nhưng khi chưa có mã hàng thì làm sao phát sinh dữ liệu cột C,D,E , lúc viết cho bạn là dữ liệu Range còn kiểu table nó sẽ khác để khắc phục như bài 6 mình đã nêu

 

[hoahuongduong1986]

Bạn có

SoGiaoDich = .Range(.[B4], .[B65000].End(3))

Tôi thì luôn viết tường minh

SoGiaoDich = .Range(.[B4], .[B65000].End(xlUp))

Nếu bạn có B4:H16 là bảng thì .[B65000].End(xlUp).Row luôn trả về giá trị 16 (.[B65000].End(xlUp) trả về B16) cho dù B12:B16 không có dữ liệu (trống). Vì thế kết quả sai.

Phải nhớ đến ngày tận thế là nếu cột "hichic" thuộc bảng thì End(...) sẽ trả về dòng cuối cùng của bảng cho dù trên nó tất cả các ô đều trống.

Có những trường hợp bạn phải đi từ dưới lên, vd. bắt đầu từ dòng 30000, và xác định dòng đầu tiên gặp có dữ liệu. Đấy là dòng cuối cùng có dữ liệu. Vd. kiểm tra trong vòng Do While ... Loop

Nếu từ B4 trở xuống có ít nhất 2 ô có dữ liệu và không có ô nào trống ở giữa, tức không thể là B4:B7 và B9 có dữ liệu còn B8 trống, nếu thế thì có thể dùng

SoGiaoDich = .Range(.[B4], .[B4].End(xlDown))

Mình thắc mắc bạn muốn code dựa vào mã hàng cột B nhưng khi chưa có mã hàng thì làm sao phát sinh dữ liệu cột C,D,E , lúc viết cho bạn là dữ liệu Range còn kiểu table nó sẽ khác để khắc phục như bài 6 mình đã nêu

Em cảm ơn anh ạ. Bác Batman1 sửa cái đoạn nhỏ trên Code của anh là nó được rồi anh ạ. Hai anh thật cái gì cũng có thể làm được !

Bài đã được tự động gộp: 30/4/19

Bạn có

SoGiaoDich = .Range(.[B4], .[B65000].End(3))

Tôi thì luôn viết tường minh

SoGiaoDich = .Range(.[B4], .[B65000].End(xlUp))

Nếu bạn có B4:H16 là bảng thì .[B65000].End(xlUp).Row luôn trả về giá trị 16 (.[B65000].End(xlUp) trả về B16) cho dù B12:B16 không có dữ liệu (trống). Vì thế kết quả sai.

Phải nhớ đến ngày tận thế là nếu cột "hichic" thuộc bảng thì End(...) sẽ trả về dòng cuối cùng của bảng cho dù trên nó tất cả các ô đều trống.

Có những trường hợp bạn phải đi từ dưới lên, vd. bắt đầu từ dòng 30000, và xác định dòng đầu tiên gặp có dữ liệu. Đấy là dòng cuối cùng có dữ liệu. Vd. kiểm tra trong vòng Do While ... Loop

Nếu từ B4 trở xuống có ít nhất 2 ô có dữ liệu và không có ô nào trống ở giữa, tức không thể là B4:B7 và B9 có dữ liệu còn B8 trống, nếu thế thì có thể dùng

SoGiaoDich = .Range(.[B4], .[B4].End(xlDown))

Anh ơi, Code đã chạy đúng khi điều chỉnh rồi ạ.

Tiện A cho em hỏi xíu này nữa thôi ạ. Với mong muốn chỉ tính công thức như dưới và cho kết quả tại cột Q từ Q2 trở xuống với điều kiện RC9<> "" còn lại nếu các địa chỉ RC9 = Rỗng thì không tính toán thì sửa thế nào ạ.


With Sheet4.Range("Q2:Q30000")
.FormulaR1C1 = "=IF(RC9="""","""",if(RC9=""VND"",RC29,IF(RC9=""USD"",RC29*'Khai bao'!R4C3,RC29*'Khai bao'!R5C3))/1000000)"
.Value = .Value

 

[batman1]

Em cảm ơn anh ạ. Bác Batman1 sửa cái đoạn nhỏ trên Code của anh là nó được rồi anh ạ.
...
Anh ơi, Code đã chạy đúng khi điều chỉnh rồi ạ.

Tôi đã nói rồi. Bạn xóa từ B5 trở xuống chỉ để lại B4 thì kết quả sai. Hôm nay bạn có tới B11 nhưng ngày mai có thể chỉ có B4. Code luôn phải viết sao cho ngày hôm nay và ngày mai đều đúng.

 

[hoahuongduong1986]

Tôi đã nói rồi. Bạn xóa từ B5 trở xuống chỉ để lại B4 thì kết quả sai. Hôm nay bạn có tới B11 nhưng ngày mai có thể chỉ có B4. Code luôn phải viết sao cho ngày hôm nay và ngày mai đều đúng.

Đây ạ,
Tại cột I - số tiền quy đổi em đã có Code tính và cho ra két quả, nhưng do cố định đến dòng I20 nên Table nó mở rộng đến dòng 20. Sửa sao để nó chỉ tính công thức này tại cột I khi mà giá trị tại các ô cột E khác rỗng ạ. Còn giá trị tại cột E bằng rỗng thì không tính toán gì ạ.