Giúp em tìm và đặt tên Range thỏa mãn 1 điều kiện cho trước (1 người xem)

[thangcola114]

Giúp em tìm và đặt tên các Range thỏa mãn các điều kiện nhất định như:
- Range giới hạn bởi giá trị của Border là nét đôi
- Cell đầu tiên (Tương tự là Cell cuối cùng) thỏa mãn 3 điều kiện sau: thuộc Range giới hạn trên và nằm trong cột được chỉ định (ví dụ là cột K) và có giá trị là chữ(Tương tự có giá trị là số)
yêu cầu chi tiết em nêu trong file đính kèm, Các bác giúp em viết code

 

[ndu96081631]

yêu cầu chi tiết em nêu trong file đính kèm, Các bác giúp em viết code

Mở file đính kèm, có thấy bạn NÊU cái gì trong đó đâu

 

[thangcola114]

Mở file đính kèm, có thấy bạn NÊU cái gì trong đó đâu

Sáng vội quá nên up nhầm file, cảm ơn bác.
Em up lại file trong bài #1 rồi, bác xem viết code giúp em

 

[thangcola114]

Bác nào vào giúp em với

 

[Hoàng Trọng Nghĩa]

Giúp em tìm và đặt tên các Range thỏa mãn các điều kiện nhất định như:
- Range giới hạn bởi giá trị của Border là nét đôi
- Cell đầu tiên (Tương tự là Cell cuối cùng) thỏa mãn 3 điều kiện sau: thuộc Range giới hạn trên và nằm trong cột được chỉ định (ví dụ là cột K) và có giá trị là chữ(Tương tự có giá trị là số)
yêu cầu chi tiết em nêu trong file đính kèm, Các bác giúp em viết code

Cho tôi hỏi kỹ các quy luật của Bảng.

1) Tiêu đề của bảng luôn luôn bắt đầu từ ô A4 (hàng 4)?

2) Ô cuối cùng, trong trường hợp này là ô B10 (hàng 10) luôn luôn là chữ TỔNG CỘNG?

3) Giữa (1) và (2) thì thêm hàng hoặc bớt hàng thoải mái?

 

[ndu96081631]

Cho tôi hỏi kỹ các quy luật của Bảng.

1) Tiêu đề của bảng luôn luôn bắt đầu từ ô A4 (hàng 4)?

2) Ô cuối cùng, trong trường hợp này là ô B10 (hàng 10) luôn luôn là chữ TỔNG CỘNG?

3) Giữa (1) và (2) thì thêm hàng hoặc bớt hàng thoải mái?

Tôi hiểu bài này như sau: Tìm Range thỏa mản điều kiện Range đó được bao quanh bởi border nét đôi
Tức tìm theo border, không liên quan gì đến dữ liệu cả
Cũng nghĩ đến Application.FindFormat nhưng thí nghiệm chưa ra

 

[Hoàng Trọng Nghĩa]

Tôi hiểu bài này như sau: Tìm Range thỏa mản điều kiện Range đó được bao quanh bởi border nét đôi
Tức tìm theo border, không liên quan gì đến dữ liệu cả
Cũng nghĩ đến Application.FindFormat nhưng thí nghiệm chưa ra

Để xem tác giả trả lời như thế nào đã Thầy, chứ tìm theo FindFormat có lẽ không tìm được. Đằng nào cũng có những quy luật của một bảng chứ!

 

[ndu96081631]

Để xem tác giả trả lời như thế nào đã Thầy, chứ tìm theo FindFormat có lẽ không tìm được. Đằng nào cũng có những quy luật của một bảng chứ!

Thì tác giả đã nói rõ rồi còn gì:

- Range giới hạn bởi giá trị của Border là nét đôi

 

[Hoàng Trọng Nghĩa]

Thì tác giả đã nói rõ rồi còn gì:

Cái đó chưa phải là một quy luật cụ thể. Một bảng có thể có tiêu đề, số cột, ... chứ nói format không thì làm sao chọn được.

 

[ndu96081631]

Cái đó chưa phải là một quy luật cụ thể. Một bảng có thể có tiêu đề, số cột, ... chứ nói format không thì làm sao chọn được.

Không có cái gì mà không làm được cả, chẳng qua chưa nghĩ ra thôi
Những "vật thể" đang tồn tại trên 1 bảng tính thì bắt buộc phải TÌM ĐƯỢC --> Nếu không Excel quản lý bằng cách nào?
Tôi làm thí nghiệm trên 1 file mới đây:

Sub FindFormat()
  Dim rng_First As Range, rng_End As Range, rng As Range
  With Application.FindFormat
    .Clear
    With .Borders(xlEdgeTop)
      .ColorIndex = -4142
      .LineStyle = xlDouble
    End With
    With .Borders(xlEdgeLeft)
      .ColorIndex = -4142
      .LineStyle = xlDouble
    End With
    Set rng_First = ActiveSheet.UsedRange.Find("", , , , , , , , True)
    .Clear
    If Not rng_First Is Nothing Then
      With .Borders(xlEdgeBottom)
        .ColorIndex = -4142
        .LineStyle = xlDouble
      End With
      With .Borders(xlEdgeRight)
        .ColorIndex = -4142
        .LineStyle = xlDouble
      End With
      Set rng_End = ActiveSheet.UsedRange.Find("", , , , , , , , True)
      If Not rng_End Is Nothing Then
        Set rng = Range(rng_First, rng_End)
        rng.Select
        MsgBox rng.Address
      End If
    End If
    .Clear
  End With
End Sub

 

[TrungChinhs]

Tôi thì luôn dựa vào dữ liệu để xác định vị trí bảng (không bao giờ dựa vào Borders). Còn muốn dựa vào Borders để xác định vị trí bảng thì có lẽ phải quét theo cells
Ví dụ: Tìm ô có đường kẻ dưới là nét đôi mảnh

Sub Macro22()
For Each cls In Range([a1], [a65536].End(3)(10))
    If cls.Borders(4).LineStyle = xlDouble Then MsgBox cls.Address
Next
End Sub

 

[thangcola114]

Cho tôi hỏi kỹ các quy luật của Bảng.

1) Tiêu đề của bảng luôn luôn bắt đầu từ ô A4 (hàng 4)?

2) Ô cuối cùng, trong trường hợp này là ô B10 (hàng 10) luôn luôn là chữ TỔNG CỘNG?

3) Giữa (1) và (2) thì thêm hàng hoặc bớt hàng thoải mái?

Em có thể tìm và đặt tên được Range([A4]:K & [65000].End(xlup)) chính là phần giới hạn bởi Giá trị là Border nét đôi.
Tuy nhiên, nhiều sheet có:
1.phần tiêu đề nằm dòng khác nhau nên vị trí của [A4] thay đổi
2.Kích thước của Range giới hạn thay đổi về số dòng và 1 số có thay đổi về cột nên em muốn tìm và phân chia bảng tính làm 2 phần:
-Phần giới hạn bởi Borders bao ngoài (giá trị là nét đôi, nét đơn..., ở đây là nét đôi) =>phần em đang muốn tìm bằng code
-Phần văn bản để thuyết minh không có Borders (thường nằm trên và dưới của phần có Border)
Sau khi tìm và đặt tên được Range sẽ tìm tiếp 1 số Cells trong Range này và thỏa mãn điều kiện nhất định:
- Thuộc cột chỉ định, ví dụ cột K
- là Cell đầu tiên Có thuộc tính là: số (hoặc điều kiện khác tùy người dùng chỉnh sửa thêm)

 

[thangcola114]

Tôi thì luôn dựa vào dữ liệu để xác định vị trí bảng (không bao giờ dựa vào Borders). Còn muốn dựa vào Borders để xác định vị trí bảng thì có lẽ phải quét theo cells
Ví dụ: Tìm ô có đường kẻ dưới là nét đôi mảnh

Sub Macro22()
For Each cls In Range([a1], [a65536].End(3)(10))
    If cls.Borders(4).LineStyle = xlDouble Then MsgBox cls.Address
Next
End Sub

Em cũng tìm thấy chủ đề về lấy địa chỉ ô đầu và ô cuối của vùng dữ liệu của anh tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?22175-Lấy-địa-chỉ-ô-đầu-ô-cuối-vùng-dữ-liệu
Tuy nhiên ô đầu trong vùng giới hạn bởi bảng thì em chưa xác định được theo cách của chủ đề trên

 

[sealand]

Tôi thì luôn dựa vào dữ liệu để xác định vị trí bảng (không bao giờ dựa vào Borders). Còn muốn dựa vào Borders để xác định vị trí bảng thì có lẽ phải quét theo cells
Ví dụ: Tìm ô có đường kẻ dưới là nét đôi mảnh

Sub Macro22()
For Each cls In Range([a1], [a65536].End(3)(10))
    If cls.Borders(4).LineStyle = xlDouble Then MsgBox cls.Address
Next
End Sub

Không Trung Chinh ơi, vì nó có phương thức findformat nên tìm nhanh hơn chứ không phải lượt hết ô. Nhưng cái chính là điều kiện theo kiểu trực quan này khó mà chính xác được. Ví dụ có 1 đường kẻ đôi giữa ô A2 và A3. Vậy nó có thể là kẻ chân của A2 hay có thể là kẻ đỉnh của A3. Mà đã không thống nhất thì chuyện sai là nhỡn tiền nên mình không tham gia được

 

[ndu96081631]

Không Trung Chinh ơi, vì nó có phương thức findformat nên tìm nhanh hơn chứ không phải lượt hết ô. Nhưng cái chính là điều kiện theo kiểu trực quan này khó mà chính xác được. Ví dụ có 1 đường kẻ đôi giữa ô A2 và A3. Vậy nó có thể là kẻ chân của A2 hay có thể là kẻ đỉnh của A3. Mà đã không thống nhất thì chuyện sai là nhỡn tiền nên mình không tham gia được

Được chứ anh!
Nếu sợ dùng FindFormat khó tìm được quy luật chung thi bùn lắm ta làm thế này:

Sub FindBorder()
  Dim rng_First As Range, rng_End As Range, rng As Range, rCel As Range
  For Each rCel In ActiveSheet.UsedRange
    If rCel.Borders(xlEdgeTop).LineStyle = xlDouble Then
      If rCel.Borders(xlEdgeLeft).LineStyle = xlDouble Then Set rng_First = rCel
    End If
    If rCel.Borders(xlEdgeRight).LineStyle = xlDouble Then
      If rCel.Borders(xlEdgeBottom).LineStyle = xlDouble Then Set rng_End = rCel
    End If
    If (Not rng_First Is Nothing) And (Not rng_End Is Nothing) Then
      Set rng = Range(rng_First, rng_End)
      rng.Select
      MsgBox rng.Address
      Exit For
    End If
  Next
End Sub

 

[TrungChinhs]

Em cũng tìm thấy chủ đề về lấy địa chỉ ô đầu và ô cuối của vùng dữ liệu của anh tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?22175-Lấy-địa-chỉ-ô-đầu-ô-cuối-vùng-dữ-liệu
Tuy nhiên ô đầu trong vùng giới hạn bởi bảng thì em chưa xác định được theo cách của chủ đề trên

Nếu ô đầu tiên của Bảng là STT và phía sau, phía dưới bảng luôn sạch (không có dữ liệu nháp) thì bảng của bạn luôn xác định được bằng code sau mà không phụ thuộc vào số dòng, số cột của bảng. Hiện nay tôi đang dùng cách này để xác định vị trí vùng dữ liệu trong bảng tính.

Sub Table()
    With ActiveSheet
        '1- O dau cua bang - phia tren ben trai
        Set RngTL = .UsedRange.Find("Stt", , , 2, 1, 1)
       
        '2- O cuoi cua bang - phia duoi ben phai
        Set RngLR = .UsedRange.Find("*", , , 2, 2, 2)
        
        '3- Dong dau cot cuoi - phia tren ben phai
        Set RngTR = .Cells(RngTL.Row, RngLR.Column)
        
        '4- Dong cuoi cot dau - phia duoi ben trai
        Set RngLL = RngTL(65000).End(3)(2)
        
        ' So dong cua bang = dong cuoi - dong dau +1
        sR = RngLR.Row - RngTL.Row + 1
        ' So cot cua bang = cot cuoi - cot dau +1
        sCl = RngLR.Column - RngTL.Column + 1
        
        'Vung du lieu (bang)
        Set Rng = RngTL.Resize(sR, sCl)
        MsgBox Rng.Address
        
        'O dau tien cua cot cuoi la dong 7
        MsgBox RngTR(4).Address
        
        'O tong cong o cuoi cot B
        MsgBox RngLL(1, 2).Address
    End With
End Sub

 

[thangcola114]

Được chứ anh!
Nếu sợ dùng FindFormat khó tìm được quy luật chung thi bùn lắm ta làm thế này:

Sub FindBorder()
  Dim rng_First As Range, rng_End As Range, rng As Range, rCel As Range
  For Each rCel In ActiveSheet.UsedRange
    If rCel.Borders(xlEdgeTop).LineStyle = xlDouble Then
      If rCel.Borders(xlEdgeLeft).LineStyle = xlDouble Then Set rng_First = rCel
    End If
    If rCel.Borders(xlEdgeRight).LineStyle = xlDouble Then
      If rCel.Borders(xlEdgeBottom).LineStyle = xlDouble Then Set rng_End = rCel
    End If
    If (Not rng_First Is Nothing) And (Not rng_End Is Nothing) Then
      Set rng = Range(rng_First, rng_End)
      rng.Select
      MsgBox rng.Address
      Exit For
    End If
  Next
End Sub

Tuyệt vời, rất cảm ơn bác ndu96081631 đã giải quyết bài toán chọn range giới hạn bởi Border bao ngoài và Set Rnd= Range này
Lúc này em có thể thao tác chọn được cells trong này Range này theo dạng Rng(m, n) là ô ở vị trí hàng m cột n trong Range này.
1. Tuy nhiên, em không hiểu nếu cells được chọn nằm trong nhiều giá trị tìm được của các hàm như hàm if (ví dụ trên là có điều kiện về thuộc tính là : số ) thì lúc đó muốn lấy giá trị đầu tiên hoặc cuối cùng trong các giá trị đó thì phải làm thế nào?
2. Bài toán chọn Range giới hạn bởi Border cũng sẽ hay gặp hơn trong nhiều bảng tính và "khoai" hơn (tất nhiên bảng tính của em thì ok rồi) khi mà :
- Nét bao ngoài và nét đứng ,dòng tiêu đề, dòng cuối cùng là nét đơn xlcontinuos
- Nét ngang là nét đứt
(em nghĩ nếu có thể lấy về giá trị đầu tiên và giá trị cuối cùng trong nhiều kết quả của hàm if về giá trị Border nét liền đơn cũng sẽ giải quyết được)

 

[thangcola114]

Nếu ô đầu tiên của Bảng là STT và phía sau, phía dưới bảng luôn sạch (không có dữ liệu nháp) thì bảng của bạn luôn xác định được bằng code sau mà không phụ thuộc vào số dòng, số cột của bảng. Hiện nay tôi đang dùng cách này để xác định vị trí vùng dữ liệu trong bảng tính.

Sub Table()
    With ActiveSheet
        '1- O dau cua bang - phia tren ben trai
        Set RngTL = .UsedRange.Find("Stt", , , 2, 1, 1)
       
        '2- O cuoi cua bang - phia duoi ben phai
        Set RngLR = .UsedRange.Find("*", , , 2, 2, 2)
        
        '3- Dong dau cot cuoi - phia tren ben phai
        Set RngTR = .Cells(RngTL.Row, RngLR.Column)
        
        '4- Dong cuoi cot dau - phia duoi ben trai
        Set RngLL = RngTL(65000).End(3)(2)
        
        ' So dong cua bang = dong cuoi - dong dau +1
        sR = RngLR.Row - RngTL.Row + 1
        ' So cot cua bang = cot cuoi - cot dau +1
        sCl = RngLR.Column - RngTL.Column + 1
        
        'Vung du lieu (bang)
        Set Rng = RngTL.Resize(sR, sCl)
        MsgBox Rng.Address
        
        'O dau tien cua cot cuoi la dong 7
        MsgBox RngTR(4).Address
        
        'O tong cong o cuoi cot B
        MsgBox RngLL(1, 2).Address
    End With
End Sub

Em sẽ tham khảo để sử dụng cho các bảng thỏa mãn điều kiện trên.
Phần border nằm giữa đoạn văn bản thì không dùng được code này của bác rồi (mà phần dưới Border của em luôn là phần chữ ký nên không bỏ được)
E thấy sử dụng findmethod và used range hay thật, nhưng giờ mới tìm hiểu vba nên thấy hơi khó hiểu.
Cái usedrange tức là vùng đã sử dụng, vậy mà trong file đã đính kèm của bài #1 em thử code của bác ndu để test thì lại ra ô cuối là L43 trong khi ô cuối có dữ liệu lại là ô thuộc hàng 14 ?

Sub Test_UsedRange()
With ActiveSheet.UsedRange
.Select
MsgBox .Address
End With
End Sub

 

[ndu96081631]

Cái usedrange tức là vùng đã sử dụng, vậy mà trong file đã đính kèm của bài #1 em thử code của bác ndu để test thì lại ra ô cuối là L43 trong khi ô cuối có dữ liệu lại là ô thuộc hàng 14 ?

Bạn cũng nói rằng UsedRange là vùng Đã sử dụng ---> Vậy chắc chắn L43 đã từng có dữ liệu rồi (hiện giờ có thể đã xóa)

 

[thangcola114]

Bạn cũng nói rằng UsedRange là vùng Đã sử dụng ---> Vậy chắc chắn L43 đã từng có dữ liệu rồi (hiện giờ có thể đã xóa)

Em cũng hiểu như vậy, tuy nhiên, nếu ngay tại dòng 44 thêm dữ liệu vào và test thì usedRange sẽ kéo xuống L44; nhưng xóa đi dữ liệu dòng 44 thì UsedRange trả về L43 (Có thể hiểu là đã sử dụng tức là đã chiếm bộ nhớ của vùng trong trường hơp này và nếu xóa đi thì sẽ không còn chiếm bộ nhớ nữa)