Hàm VBA tự thay đổi Range khi Sheet excel được insert thêm dòng

[pham ha 94]

Tại excel ô A1, A2, A3 có giá trị
Khi cần tham chiếu đến giá trị A2, A3 VBA sẽ dùng Range(“A2:A3”)
Khi excel insert thêm dòng, vùng A2:A3 sẽ thành A3:A4 nhưng công thức VBA vẫn không đổi
Nhờ các bác tư vấn giúp “Có cách nào để lúc insert thêm dòng sẽ không bị lỗi, giảm việc sửa code không?

 

[Hoàng Tuấn 868]

Tại excel ô A1, A2, A3 có giá trị
Khi cần tham chiếu đến giá trị A2, A3 VBA sẽ dùng Range(“A2:A3”)
Khi excel insert thêm dòng, vùng A2:A3 sẽ thành A3:A4 nhưng công thức VBA vẫn không đổi
Nhờ các bác tư vấn giúp “Có cách nào để lúc insert thêm dòng sẽ không bị lỗi, giảm việc sửa code không?

View attachment 288013View attachment 288012

Nghiên cứu cách tìm dòng cuối trong vba.

 

[pham ha 94]

Ý mình là nếu dưới dòng A2: A3 còn rất nhiều dòng nữa, tìm dòng cuối không có ý nghĩa gì cả

 

[Hoàng Tuấn 868]

Ý mình là nếu dưới dòng A2: A3 còn rất nhiều dòng nữa, tìm dòng cuối không có ý nghĩa gì cả

Bạn nên đưa file có dữ liệu cụ thể.

chưa hiểu ý.
Range(“A2:A3”) -> Range("A2:A"&Cells(Rows.Count, "A").End(xlUp).Row)

Ý mình là nếu dưới dòng A2: A3 còn rất nhiều dòng nữa, tìm dòng cuối không có ý nghĩa gì cả

Ý chủ bài đây cơ mà.

 

[SA_DQ]

Tại excel ô A1, A2, A3 có giá trị
Khi cần tham chiếu đến giá trị A2, A3 VBA sẽ dùng Range(“A2:A3”)
Khi excel insert thêm dòng, vùng A2:A3 sẽ thành A3:A4 nhưng công thức VBA vẫn không đổi
Nhờ các bác tư vấn giúp “Có cách nào để lúc insert thêm dòng sẽ không bị lỗi, giảm việc sửa code không?

Gán thử cho vùng, giả sử [A2:A3] 1 cái tên như "GPE"
Mong phù hợp với iêu cầu của bạn!

 

[cantl]

Ý mình là nếu dưới dòng A2: A3 còn rất nhiều dòng nữa, tìm dòng cuối không có ý nghĩa gì cả

Nếu không ngại mất thời gian thì dùng Inputbox. Nhưng khuyên là không nên vì mỗi lần như vậy mất tầm 3s cuộc đời, làm nhiều lần thì cộng dồn lại cũng bỏ lỡ nhiều năm.

 

[Hoàng Tuấn 868]

"tìm dòng cuối không có ý nghĩa gì cả"

Nếu không ngại mất thời gian thì dùng Inputbox. Nhưng khuyên là không nên vì mỗi lần như vậy mất tầm 3s cuộc đời, làm nhiều lần thì cộng dồn lại cũng bỏ lỡ nhiều năm.

Cái này là do người ta chưa hiểu cách làm nên mới phát biểu như vậy. Nếu đưa dữ liệu lên xem cụ thể thế nào thì sẽ giải quyết được thôi mà

 

[ptm0412]

Câu hỏi như vậy là rõ ràng rồi.
Nếu chèn dòng tại dòng n:
Trước khi chèn: gán giá trị An vào 1 biến "tam"
Đứng tại dòng n thực hiện chèn dòng
Sau khi chèn:
Dùng phương thức find tìm ô có chứa giá trị biến "tam" trong cột A
Từ vị trí của ô tìm thấy, offset, resize này nọ cho ra range mới
Xử lý range mới (xóa sửa, ...)
-------
Cách này dùng cho số dòng chèn vào bất kỳ (chèn 5, 7 dòng cũng vẫn đúng)

 

[VetMini]

Câu hỏi như vậy là rõ ràng rồi.
...

Chưa rõ!!!
1. Việc "chèn dòng" của thớt là thủ công hay do code chèn?
2. Nếu chèn dòng giữa range thì sao? Chọn rows + 1, hay chọn rows kể từ row1, hay row1+1?
3. Nếu không phải chèn mà là xóa thì sao?

 

[ptm0412]

Chưa rõ!!!
1. Việc "chèn dòng" của thớt là thủ công hay do code chèn?
2. Nếu chèn dòng giữa range thì sao? Chọn rows + 1, hay chọn rows kể từ row1, hay row1+1?
3. Nếu không phải chèn mà là xóa thì sao?

1. Chèn thủ công hay bằng code thì cũng vậy. Nếu chèn thủ công thì 2 sub và Biến "tam" khai báo public
2. Chèn tại dòng n (đứng tại dòng n để chèn). Chèn mấy dòng cũng được (m dòng). Ghi lại cho rõ là "Dùng phương thức find tìm ô có chứa giá trị biến "tam" trong cột A" từ dòng n trở xuống (lúc mới chèn xong thì activecell vẫn là dòng n, giá trị cũ của An bị đẩy xuống m dòng trắng.
3. Bài 1 viết là "insert", không phải xóa.

---------
Câu trả lời là đứng tại ô tìm thấy, offset resize các kiểu, nghĩa là nhiều kiểu. Trong đó có cả các kiểu:
- Giữ nguyên Range cũ không thay đổi địa chỉ, do n > row cuối của range cũ
- offset xuống m dòng khi n <= dòng đầu của range cũ
- không offset, resize dòng = số dòng cũ + m khi dòng đầu range cũ < n <= dòng cuối range cũ
- ... các kiểu khác

 

[VetMini]

2. Chèn tại dòng n (đứng tại dòng n để chèn). Chèn mấy dòng cũng được (m dòng). Ghi lại cho rõ là "Dùng phương thức find tìm ô có chứa giá trị biến "tam" trong cột A" từ dòng n trở xuống (lúc mới chèn xong thì activecell vẫn là dòng n, giá trị cũ của An bị đẩy xuống m dòng trắng.

Range là "a10:a11"
a10 chứa "x"
a8 cũng chứa "x"
chèn 1 dòng ở dòng 5.
Find("x") từ 5 trở xuống sẽ cho ra a9 (a8 cũ)

Giải thuật tạm:
Đặt "a1:a3" vào một nmaed range nrg1.
Đặt "a2:a3" vào một named range nrgTarget.
Nếu dòng chèn intersects nrg1 thì sửa ngr1 và nrgTarget theo số dòng chèn.

 

[SA_DQ]

Nếu chèn dòng tại dòng n:
Trước khi chèn: gán giá trị An vào 1 biến "tam" . . . . .

Giả dụ dữ liệu không chuẩn & tham biến 'Tam' bị chứa 'RỔNG'

Thì câu này bập bênh lắm:

Dùng phương thức find tìm ô có chứa giá trị biến "tam" trong cột A

 

[ptm0412]

Range là "a10:a11"
a10 chứa "x"
a8 cũng chứa "x"

Anh nói đúng khi mở rộng vấn đề. Còn giải thuật của tôi dựa trên cái hình ở bài 1: Cột A không trùng.
Ai biểu không đưa file, chỉ đưa hình làm chi.

Giả dụ dữ liệu không chuẩn & tham biến 'Tam' bị chứa 'RỔNG'

Thì câu này bập bênh lắm:

Anh nói cũng đúng. Nhưng hình bài 1 cho thấy nó có cái gì đó cần xóa, chứ không rỗng.

 

[VetMini]

Thực ra, nếu yêu cầu của thớt chỉ giản dị có vậy thì cách giản dị nhất là dùng Name. Khi chèn dòng, name se tự động tháy đổi.

Theo code của thớt, nếu thớt đặt name KhiMocKho = "[Sheet1}!$a$2:$a$3"
thì khi thêm dòng ở a1, KhiMoKho sẽ tự động thánh a3:a4

 

[SA_DQ]

Name 'KhiMocKho' này cũng giống với hướng dẫn #5; Nhưng chủ bài đăng chưa phản ứng!

 

[Maika8008]

Name 'KhiMocKho' này cũng giống với hướng dẫn #5; Nhưng chủ bài đăng chưa phản ứng!

Chủ thớt hình như chẳng quan tâm gì mấy đến thớt của mình. Kể từ bài #3 là lặn mất không thấy sủi tăm.

 

[pham ha 94]

Gán thử cho vùng, giả sử [A2:A3] 1 cái tên như "GPE"
Mong phù hợp với iêu cầu của bạn!

Cảm ơn bác,cách này e thấy đúng với mong muốn rồi ạ

Thực ra, nếu yêu cầu của thớt chỉ giản dị có vậy thì cách giản dị nhất là dùng Name. Khi chèn dòng, name se tự động tháy đổi.

Theo code của thớt, nếu thớt đặt name KhiMocKho = "[Sheet1}!$a$2:$a$3"
thì khi thêm dòng ở a1, KhiMoKho sẽ tự động thánh a3:a4

Cảm ơn bác, Cách này, khi insert dòng thì VBA vẫn sẽ tự tìm name là đoạn A2:A3 thành A3:A4. rồi

Chủ thớt hình như chẳng quan tâm gì mấy đến thớt của mình. Kể từ bài #3 là lặn mất không thấy sủi tăm.

Cuối tuần vừa rồi, mình đi công tác bên Trung nên mạng nó xa tầm với quá.
Cảm ơn bác nhiều, Bác SA_DQ có hướng dẫn theo hướng đặt name khác hay ạ