Xác định vị trí tương đối Range và công thức

[viehoai]

Trong quá trình "luyện" về mãng đã nảy sinh vấn đề tưởng chừng đơn giản nhưng mình tìm mãi vẫn chưa ra:
Xác định vị trí tương đối của Range. Cụ thể như

Sub AddressCellOfRange()
Dim Rng As Range, MyRange As Range
Set MyRange = Range("C10:E20")
For Each Rng In MyRange.SpecialCells(xlCellTypeFormulas, 23)
  Xác định hàng và cột của Rng??
Next
End Sub

Xin cảm ơn các anh chị

 

[ndu96081631]

Trong quá trình "luyện" về mãng đã nảy sinh vấn đề tưởng chừng đơn giản nhưng mình tìm mãi vẫn chưa ra:
Xác định vị trí tương đối của Range. Cụ thể như

Sub AddressCellOfRange()
Dim Rng As Range, MyRange As Range
Set MyRange = Range("C10:E20")
For Each Rng In MyRange.SpecialCells(xlCellTypeFormulas, 23)
  Xác định hàng và cột của Rng??
Next
End Sub

Xin cảm ơn các anh chị

Cho file lên luôn đi cho dễ hình dung (ghi rõ cần "xuất cái gì" và "xuất vào chổ nào")

 

[viehoai]

Cho file lên luôn đi cho dễ hình dung (ghi rõ cần "xuất cái gì" và "xuất vào chổ nào")

Ý em như sau: Cho Rng duyệt qua từng phần tử trong MyRange. Nếu Rng là công thức thì xác định hàng và cột tương đối của Rng nằm ở hàng thứ mấy, cột mấy của MyRange (so với MyRang chứ không phải xác định hàng và cột của Rng trong bảng tính).

 

[ptm0412]

Vậy tại sao không for i theo dòng và for j theo cột? Kết quả cần tìm là i và j đấy.

 

[viehoai]

Vậy tại sao không for i theo dòng và for j theo cột? Kết quả cần tìm là i và j đấy.

Dạ, em đã làm cái này nhưng vấn đề ở chỗ Mình cho Rng duyệt qua từng phần tử của MyRange nếu MyRange dữ liệu lớn liệu có chậm không? Trong khi đó ta duyệt Rng qua từng phần tử chứa công thức của MyRange (nên em dùng

For Each Rng In MyRange.SpecialCells(xlCellTypeFormulas, 23))​

em nghĩ sẽ nhanh hơn nhiều, vậy trường hợp này code ra sao Sư Phụ giúp em với nhé. Em cảm ơn

 

[ptm0412]

Vậy thì dùng mảng, nhưng không gán value cho mảng, mà gán text.
Đại khái:

Dim Arr
Arr = Range("C10:E20").Text
For i = 1 to Ubound(Arr, 1)
   For j = 1 to UBound(Arr, 2)
      If left(Arr(i, j),1) = "=" Then
          Msgbox i & Chr(10) & j
          'Viết chay, phải test lại'

Khoan, để test cái đã!

 

[viehoai]

Vậy thì dùng mảng, nhưng không gán value cho mảng, mà gán text.
Đại khái:

Dim Arr
Arr = Range("C10:E20").Text
For i = 1 to Ubound(Arr, 1)
   For j = 1 to UBound(Arr, 2)
      If left(Arr(i, j),1) = "=" Then
          Msgbox i & Chr(10) & j
          'Viết chay, phải test lại'

Khoan, để test cái đã!

Lỗi Type mismatch tại For i = 1 to Ubound(Arr, 1) Sư phụ ơi. Nhưng trông có vẽ như cách bài #4 ?

 

[ptm0412]

Type MisMatch ở dòng gán Range().Text
Tuy nhiên gán Range().Formula thì OK

Sub test()
Dim Arr()
Arr = Range("A1:E20").Formula
For i = 1 To UBound(Arr, 1)
   For j = 1 To UBound(Arr, 2)
      If Left(Arr(i, j), 1) = "=" Then
          MsgBox i & Chr(10) & j
      End If
  Next
Next
End Sub

Tất nhiên là giống bài 4, nhưng dùng mảng để tăng tốc độ theo yêu cầu bài 5.

 

[concogia]

Nếu làm bài này dùng mảng, cho chạy hết dữ liệu với 2 thằng For với mục đích học & ôn bài thì mình Ok, còn nếu phải làm vì công việc thì mình chọn phương án của VietHoai là chỉ chạy trên những cell có công thức. Mình không biết tốc độ khi chạy với dữ liệu lớn ra sao, chỉ nghĩ chắc nó cũng xem xem nhau thôi (còn tùy công thức trong vùng dữ liệu nhiều hay ít nữa )

Public Sub DiaChi()
    Dim Vung, Cll, Dau, Duoi, Hang, Cot, Kq(), K
    Set Vung = Range([C10], [C50000].End(xlUp)).Resize(, Range([C10], [C10].End(xlToRight)).Columns.Count)
    Dau = Split(Vung(1, 1).Address, "$")
    ReDim Kq(1 To Vung.SpecialCells(xlCellTypeFormulas, 23).Cells.Count, 1 To 2)
        For Each Cll In Vung.SpecialCells(xlCellTypeFormulas, 23)
            Duoi = Split(Cll.Address, "$")
            Hang = Val(Duoi(2)) - Val(Dau(2)) + 1
            Cot = VBA.Asc(Duoi(1)) - Asc(Dau(1)) + 1
            'MsgBox "Dia chi: " & Cll.Address & "    " & "Hàng:  " & Hang & "     Cot:   " & Cot
            K = K + 1
            Kq(K, 1) = Cll.Address: Kq(K, 2) = Hang & ", " & Cot
        Next Cll
   [A2].Resize(K, 2) = Kq
End Sub

Híc

 

[viehoai]

Nếu làm bài này dùng mảng, cho chạy hết dữ liệu với 2 thằng For với mục đích học & ôn bài thì mình Ok, còn nếu phải làm vì công việc thì mình chọn phương án của VietHoai là chỉ chạy trên những cell có công thức. Mình không biết tốc độ khi chạy với dữ liệu lớn ra sao, chỉ nghĩ chắc nó cũng xem xem nhau thôi (còn tùy công thức trong vùng dữ liệu nhiều hay ít nữa )

Public Sub DiaChi()
    Dim Vung, Cll, Dau, Duoi, Hang, Cot, Kq(), K
    Set Vung = Range([C10], [C50000].End(xlUp)).Resize(, Range([C10], [C10].End(xlToRight)).Columns.Count)
    Dau = Split(Vung(1, 1).Address, "$")
    ReDim Kq(1 To Vung.SpecialCells(xlCellTypeFormulas, 23).Cells.Count, 1 To 2)
        For Each Cll In Vung.SpecialCells(xlCellTypeFormulas, 23)
            Duoi = Split(Cll.Address, "$")
            Hang = Val(Duoi(2)) - Val(Dau(2)) + 1
            Cot = VBA.Asc(Duoi(1)) - Asc(Dau(1)) + 1
            'MsgBox "Dia chi: " & Cll.Address & "    " & "Hàng:  " & Hang & "     Cot:   " & Cot
            K = K + 1
            Kq(K, 1) = Cll.Address: Kq(K, 2) = Hang & ", " & Cot
        Next Cll
   [A2].Resize(K, 2) = Kq
End Sub

Híc

Ah, sao ta không dùng Hang= Cll.Row-Vung.Row+1, Cot= Cll.Column-Vung..Column+1 ?
Cảm ơn anh nhiều, em cũng nghĩ chắc tốc độ giống bài của Anh Mỹ, thậm chí nếu công thức nhiều còn chậm hơn chăng???

 

[ptm0412]

Bài 4 và bài 8 nhằm đáp ứng việc lấy vị trí tương đối của cell chứa công thức trong vùng chọn, khẳng định lại bằng bài 10 của viehoai.
Code chỉ có If ... Then và không có Else.

Còn lấy vị trí đó để làm gì thì chưa biết. Do đó tốc độ code liên quan đến:
- Kích thước vùng dữ liệu thực cần xử lý
- Số lượng cells chứa công thức trong vùng đó
- Làm gì tiếp theo với vị trí tìm được
- Code (thuật toán và công cụ) xử lý vị trí tìm được theo yêu cầu.

Nếu If có Else, thì mảng vẫn là số 1.

Ngoài ra, dám cá là với vùng dữ liệu lớn, dù số lượng cells chứa công thức nhiều hay ít (đừng ít quá), dùng mảng cũng nhanh hơn.

 

[ndu96081631]

Dạ, em đã làm cái này nhưng vấn đề ở chỗ Mình cho Rng duyệt qua từng phần tử của MyRange nếu MyRange dữ liệu lớn liệu có chậm không? Trong khi đó ta duyệt Rng qua từng phần tử chứa công thức của MyRange (nên em dùng

For Each Rng In MyRange.SpecialCells(xlCellTypeFormulas, 23))​

em nghĩ sẽ nhanh hơn nhiều, vậy trường hợp này code ra sao Sư Phụ giúp em với nhé. Em cảm ơn

Vậy thì vầy xem:

Sub AddressCellOfRange()
  Dim Rng As Range, MyRange As Range
  Set MyRange = Range("C10:E20")
  For Each Rng In MyRange.SpecialCells(xlCellTypeFormulas, 23)
    MsgBox Rng.Row - MyRange.Row + 1 & vbLf & _
           Rng.Column - MyRange.Column + 1
  Next
End Sub

Ví trí dòng của Rng trừ cho vị trí dòng của MyRange rồi cộng thêm 1 là ra

 

[viehoai]

Bài 4 và bài 8 nhằm đáp ứng việc lấy vị trí tương đối của cell chứa công thức trong vùng chọn, khẳng định lại bằng bài 10 của viehoai.
Code chỉ có If ... Then và không có Else.

Còn lấy vị trí đó để làm gì thì chưa biết. Do đó tốc độ code liên quan đến:
- Kích thước vùng dữ liệu thực cần xử lý
- Số lượng cells chứa công thức trong vùng đó
- Làm gì tiếp theo với vị trí tìm được
- Code (thuật toán và công cụ) xử lý vị trí tìm được theo yêu cầu.

Nếu If có Else, thì mảng vẫn là số 1.

Ngoài ra, dám cá là với vùng dữ liệu lớn, dù số lượng cells chứa công thức nhiều hay ít (đừng ít quá), dùng mảng cũng nhanh hơn.

Dạ, từ khi em tiếp xúc với mãng, em đang bị "mê" nên tự tin với tốc độ của nó

 

[viehoai]

Quay lại bài #300 và được anh ThuNghi giải quyết bài #301. Để duy trì lại công thức tương đối của mãng ban đầu có 3 trường hợp:
1. Công thức dạng: D3=C3*B3
2. Công thức dạng: D3=Sheet2!A1
3. Công thức dạng: D3=B9+Sheet2!A2 (bao gồm cả 2 trường hợp trên)
Tôi đã xử lý 2 trường hợp trên theo code file đính kèm. Vậy trong trường hợp thứ 3 giải quyết như thế nào. Xin cảm ơn các anh chị