VBA không cập nhật địa chỉ ô trong excel khi chèn thêm dòng hoặc cột

[yeu doi]

Cho tôi hỏi: Code trong VBA đã chạy tốt nhưng khi có nhu cầu dời hoặc chèn thêm dòng/cột trong bảng tính exel thì khi chạy VBA kết quả không hiểu địa chỉ ô đã dời đi. Có cách nào cho VBA cập nhật tự động được không? Cảm ơn!

 

[Hai Lúa Miền Tây]

Cho tôi hỏi: Code trong VBA đã chạy tốt nhưng khi có nhu cầu dời hoặc chèn thêm dòng/cột trong bảng tính exel thì khi chạy VBA kết quả không hiểu địa chỉ ô đã dời đi. Có cách nào cho VBA cập nhật tự động được không? Cảm ơn!

Câu trả lời là có thể được.

 

[HeSanbi]

Có hai cách cho bác tham khảo:

1. Đặt name cho các ô cần tham chiếu trong VBA từ Name Manager (Cách này phức tạp nếu quá nhiều ô cần đặt name)
Set RG = ThisWorkbook.Names("Cell1").RefersToRange




2. Dùng vùng lặp tìm kiếm ô có giá trị là đầu đề, đề mục trong vùng nhất định, và giá trị cần tìm phải là duy nhất trong vùng cần tìm

Set RG = TargetByValue(Worksheets(1).Range("A1"), "Total", 1)

Function TargetByValue(Optional ByVal FirstTarget As Excel.Range, _
                      Optional ByVal value, _
                      Optional ByVal OffsetDown& = 0, _
                      Optional ByVal OffsetRight& = 0, _
                      Optional ByVal RowsCount& = 50, _
                      Optional ByVal ColumnsCount& = 50) As Excel.Range
  On Error GoTo E
  Dim r&, c%, Rng As Excel.Range
  Set Rng = FirstTarget
  For c = 1 To ColumnsCount
    For r = 1 To RowsCount
      If LCase(CStr(Rng(r, c).value)) = LCase(CStr(value)) Then
        Set TargetByValue = Rng(r + OffsetDown, c + OffsetRight): Exit Function
      End If
    Next
  Next
E:
End Function

 

[yeu doi]

Có hai cách cho bác tham khảo:

1. Đặt name cho các ô cần tham chiếu trong VBA từ Name Manager (Cách này phức tạp nếu quá nhiều ô cần đặt name)
Set RG = ThisWorkbook.Names("Cell1").RefersToRange

View attachment 256688


2. Dùng vùng lặp tìm kiếm ô có giá trị là đầu đề, đề mục trong vùng nhất định, và giá trị cần tìm phải là duy nhất trong vùng cần tìm

Set RG = TargetByValue(Worksheets(1).Range("A1"), "Total", 1)

Function TargetByValue(Optional ByVal FirstTarget As Excel.Range, _
                      Optional ByVal value, _
                      Optional ByVal OffsetDown& = 0, _
                      Optional ByVal OffsetRight& = 0, _
                      Optional ByVal RowsCount& = 50, _
                      Optional ByVal ColumnsCount& = 50) As Excel.Range
  On Error GoTo E
  Dim r&, c%, Rng As Excel.Range
  Set Rng = FirstTarget
  For c = 1 To ColumnsCount
    For r = 1 To RowsCount
      If LCase(CStr(Rng(r, c).value)) = LCase(CStr(value)) Then
        Set TargetByValue = Rng(r + OffsetDown, c + OffsetRight): Exit Function
      End If
    Next
  Next
E:
End Function

Cảm ơn bạn HeSanbi