Cách viết hàm R1C1 trong VBA tìm dòng cuối

Liên hệ QC
LamNA

LamNA

Thành viên tích cực
Tham gia
3/6/14
Bài viết
897
Được thích
720
Giới tính
Nam
Nghề nghiệp
Quản Lý Cửa Hàng
Chào anh chị
Em đang thử tập viết hàm R1C1 đang gặp vấn đề chưa giải quyết được, nhờ anh chị hỗ trợ giúp em nhe
- Làm sao để gán 1 dòng và lấy dòng cuối cùng trong hàm sumifs và cách viết

Mã: 
'Lỗi '
Sub TEST()
Dim I As Integer
Dim Sale As Range, NH As Range, T As Range, DT As Range
Sale = Sheet1.Range("A65536").End(xlUp).Row
NH = Sheet1.Range("B65536").End(xlUp).Row
T = Sheet1.Range("C65536").End(xlUp).Row
DT = Sheet1.Range("D65536").End(xlUp).Row
With [B3:B13]
.FormulaR1C1 = "=SUMIFS(SHEET1!R" & DT & "C4,SHEET1!R[" & Sale & "]C1,RC1,SHEET1!R[" & T & "]C3,R1C2)"
.Value = .Value
End With
End Sub

'Test 2 chạy ok nhưng lấy hết 1 cột'
Sub TEST2()

With [B3:B13]
.FormulaR1C1 = "=SUMIFS(SHEET1!RC4,SHEET1!RC1,RC1,SHEET1!RC3,R1C2)"
.Value = .Value
End With
End Sub
 

File đính kèm

  • Test R1C1.xlsb
    18.7 KB · Đọc: 20
Sắp xếp theo thời gian sắp xếp theo bầu chọn
LamNA đã viết:
Chào anh chị
Em đang thử tập viết hàm R1C1 đang gặp vấn đề chưa giải quyết được, nhờ anh chị hỗ trợ giúp em nhe
- Làm sao để gán 1 dòng và lấy dòng cuối cùng trong hàm sumifs và cách viết

Mã: 
'Lỗi '
Sub TEST()
Dim I As Integer
Dim Sale As Range, NH As Range, T As Range, DT As Range
Sale = Sheet1.Range("A65536").End(xlUp).Row
NH = Sheet1.Range("B65536").End(xlUp).Row
T = Sheet1.Range("C65536").End(xlUp).Row
DT = Sheet1.Range("D65536").End(xlUp).Row
With [B3:B13]
.FormulaR1C1 = "=SUMIFS(SHEET1!R" & DT & "C4,SHEET1!R[" & Sale & "]C1,RC1,SHEET1!R[" & T & "]C3,R1C2)"
.Value = .Value
End With
End Sub
Nhấp chuột vào đây để mở rộng...

Lỗi là do gán sai kiểu biến ạ, bạn thử sửa:
Dim Sale As Range, NH As Range, T As Range, DT As Range
thành:
Dim Sale As Long, NH As Long, T As Long, DT As Long xem được ạ.
Còn theo OT chỉ cần sử dụng 1 biến để xác định dòng cuối thôi ạ, vì về cơ bản dữ liệu đã có cột A thì các cột khác cũng sẽ có:
Mã: 
Sub TEST()
    Dim i As Long
    i = Sheet1.Range("A65536").End(xlUp).Row
    If i < 3 Then Exit Sub
    With Sheet2.Range("B3:B13")
        .Value = "=SUMIFS(Sheet1!R3C[2]:R" & i & "C[2],Sheet1!R3C[-1]:R" & i & "C[-1],RC[-1],Sheet1!R3C[1]:R" & i & "C[1],R1C)"
        '.Value = .Value
    End With
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Lỗi là do gán sai kiểu biến ạ, bạn thử sửa:
Dim Sale As Range, NH As Range, T As Range, DT As Range
thành:
Dim Sale As Long, NH As Long, T As Long, DT As Long xem được ạ.
Còn theo OT chỉ cần sử dụng 1 biến để xác định dòng cuối thôi ạ, vì về cơ bản dữ liệu đã có cột A thì các cột khác cũng sẽ có:
Mã: 
Sub TEST()
    Dim i As Long
    i = Sheet1.Range("A65536").End(xlUp).Row
    If i < 3 Then Exit Sub
    With Sheet2.Range("B3:B13")
        .Value = "=SUMIFS(Sheet1!R3C[2]:R" & i & "C[2],Sheet1!R3C[-1]:R" & i & "C[-1],RC[-1],Sheet1!R3C[1]:R" & i & "C[1],R1C)"
        '.Value = .Value
    End With
End Sub
Nhấp chuột vào đây để mở rộng...
À code của chị ok không để ý
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Lỗi là do gán sai kiểu biến ạ, bạn thử sửa:
Dim Sale As Range, NH As Range, T As Range, DT As Range
thành:
Dim Sale As Long, NH As Long, T As Long, DT As Long xem được ạ.
Còn theo OT chỉ cần sử dụng 1 biến để xác định dòng cuối thôi ạ, vì về cơ bản dữ liệu đã có cột A thì các cột khác cũng sẽ có:
Mã: 
Sub TEST()
    Dim i As Long
    i = Sheet1.Range("A65536").End(xlUp).Row
    If i < 3 Then Exit Sub
    With Sheet2.Range("B3:B13")
        .Value = "=SUMIFS(Sheet1!R3C[2]:R" & i & "C[2],Sheet1!R3C[-1]:R" & i & "C[-1],RC[-1],Sheet1!R3C[1]:R" & i & "C[1],R1C)"
        '.Value = .Value
    End With
End Sub
Nhấp chuột vào đây để mở rộng...
Code này muốn chạy trên Office 365 và Office Online như thế nào vậy OT
 
Upvote 0
HeSanbi đã viết:
Chắc vì nó miễn phí, nên không thích sử dụng?
Nhấp chuột vào đây để mở rộng...
Vấn đề quan trọng là có nhu cầu sử dụng hay không mà thôi. Nếu có nhu cầu thì chi phí bao nhiêu cũng không thành vấn đề, nếu không có nhu cầu có cho không cũng không sử dụng
 
Lần chỉnh sửa cuối:
Upvote 0
phihndhsp đã viết:
Vấn đề quan trọng là có nhu cầu sử dụng hay không mà thôi. Nếu có nhu cầu thì chi phí bao nhiêu cũng không thành vấn đề, nếu không có nhu cầu có cho không cũng không sử dụng
Nhấp chuột vào đây để mở rộng...
Nhu cầu sử dụng đối với một số người vẫn chưa quan trọng bằng nhu cẩu khoe nghề.
Có những người khoái sử dụng đủ mọi thứ để có dịp khoe "cái này tôi cũng rành".

Điển hình, tôi có nhu cầu khoe nghề ruột của mình cho nên khoái sử dụng Văn Học Cổ Hy Lạp :D. Bạn thử đụng vào Sophocles xem tôi gáy cho đến sáng.:p:p:p
 
Upvote 0
Anh chị cho em hỏi ham R1C1 này có điểm yếu gì mà em thấy ít ai sử dụng, đôi lúc áp dụng em thấy nó cũng hay
Em test thử: như vậy có được cho là nhanh không?

- Số dòng tính toán: 1,2323
- Kết quả tính toán 3 cột: 3,693 dòng
- Dữ Liệu DATA: 608,258 dòng
=> kết quả khoảng 92 giây
 

File đính kèm

  • Test R1C1.xlsb
    2.8 MB · Đọc: 27
Lần chỉnh sửa cuối:
Upvote 0
LamNA đã viết:
Anh chị cho em hỏi ham R1C1 này có điểm yếu gì mà em thấy ít ai sử dụng, đôi lúc áp dụng em thấy nó cũng hay
Em test thử: như vậy có được cho là nhanh không?

- Số dòng tính toán: 1,2323
- Kết quả tính toán 3 cột: 3,693 dòng
- Dữ Liệu DATA: 608,258 dòng
=> kết quả khoảng 92 giây
Nhấp chuột vào đây để mở rộng...
Thử code
Mã: 
Sub TEST2()
  Dim sArr(), dArr(), Res(), iKey As String, thang As Long, i As Long
  thang = Range("B1").Value
  sArr = Range("A3", Range("A1000000").End(xlUp)).Value
  ReDim Res(1 To UBound(sArr), 1 To 3)
  With Sheets("Sheet1")
    dArr = .Range("A3", .Range("D1000000").End(xlUp)).Value
  End With
  With CreateObject("scripting.dictionary")
    For i = 1 To UBound(dArr)
      If thang = dArr(i, 3) Then
        iKey = dArr(i, 1)
        If Len(iKey) > 0 Then
          .Item(iKey) = .Item(iKey) + dArr(i, 4)
        End If
      End If
    Next i
    For i = 1 To UBound(sArr)
      Res(i, 1) = .Item(sArr(i, 1))
      Res(i, 2) = Res(i, 1)
      Res(i, 3) = Res(i, 1)
    Next i
  End With
  Range("B3:D3").Resize(UBound(Res)) = Res
End Sub
 
Upvote 0
HieuCD đã viết:
Thử code
Mã: 
Sub TEST2()
  Dim sArr(), dArr(), Res(), iKey As String, thang As Long, i As Long
  thang = Range("B1").Value
  sArr = Range("A3", Range("A1000000").End(xlUp)).Value
  ReDim Res(1 To UBound(sArr), 1 To 3)
  With Sheets("Sheet1")
    dArr = .Range("A3", .Range("D1000000").End(xlUp)).Value
  End With
  With CreateObject("scripting.dictionary")
    For i = 1 To UBound(dArr)
      If thang = dArr(i, 3) Then
        iKey = dArr(i, 1)
        If Len(iKey) > 0 Then
          .Item(iKey) = .Item(iKey) + dArr(i, 4)
        End If
      End If
    Next i
    For i = 1 To UBound(sArr)
      Res(i, 1) = .Item(sArr(i, 1))
      Res(i, 2) = Res(i, 1)
      Res(i, 3) = Res(i, 1)
    Next i
  End With
  Range("B3:D3").Resize(UBound(Res)) = Res
End Sub
Nhấp chuột vào đây để mở rộng...
Nếu xét thêm nhiều điều kiện thì code sẽ thêm sao anh?
Ví dụ data có thêm 1 cột ngày và điều kiện ở
Ngay = Range("D1").Value
Em cám ơn
 
Upvote 0
LamNA đã viết:
Nếu xét thêm nhiều điều kiện thì code sẽ thêm sao anh?
Ví dụ data có thêm 1 cột ngày và điều kiện ở
Ngay = Range("D1").Value
Em cám ơn
Nhấp chuột vào đây để mở rộng...
Mã: 
    For i = 1 To UBound(dArr)
      If thang = dArr(i, 3) and ngay = dArr(i, ?) Then       
        iKey = dArr(i, 1)
        If Len(iKey) > 0 Then
          .Item(iKey) = .Item(iKey) + dArr(i, 4)
        End If       
      End If
    Next i
 
Upvote 0
LamNA đã viết:
Anh chị cho em hỏi ham R1C1 này có điểm yếu gì mà em thấy ít ai sử dụng, đôi lúc áp dụng em thấy nó cũng hay
Em test thử: như vậy có được cho là nhanh không?

- Số dòng tính toán: 1,2323
- Kết quả tính toán 3 cột: 3,693 dòng
- Dữ Liệu DATA: 608,258 dòng
=> kết quả khoảng 92 giây
Nhấp chuột vào đây để mở rộng...

Bởi vì trông nó rất xấu và khó nhìn, hoặc là bạn có thể viết theo cách này.
Mã: 
Sub TEST()
    Dim i As Long
    Const shLink As String = "Sheet1"
    i = Sheet1.Range("A65536").End(xlUp).Row
    If i < 3 Then Exit Sub
    With Sheet2.Range("B3:B13")
        .Value = "=SUMIFS(" & shLink & "!D$3:D$" & i & "," & _
                              shLink & "!A$3:A$" & i & ",A3," & _
                              shLink & "!C$3:C$" & i & ",B$1)"
        '.Value = .Value
    End With
End Sub
Với 92 giây thì thôi bạn à đùng dùng công thức nữa mà nên sử dụng mảng theo cách của bác Hiếu ở trên.
 
Upvote 0
Nguyễn Hoàng Oanh Thơ đã viết:
Bởi vì trông nó rất xấu và khó nhìn, hoặc là bạn có thể viết theo cách này.
Mã: 
Sub TEST()
    Dim i As Long
    Const shLink As String = "Sheet1"
    i = Sheet1.Range("A65536").End(xlUp).Row
    If i < 3 Then Exit Sub
    With Sheet2.Range("B3:B13")
        .Value = "=SUMIFS(" & shLink & "!D$3:D$" & i & "," & _
                              shLink & "!A$3:A$" & i & ",A3," & _
                              shLink & "!C$3:C$" & i & ",B$1)"
        '.Value = .Value
    End With
End Sub
Với 92 giây thì thôi bạn à đùng dùng công thức nữa mà nên sử dụng mảng theo cách của bác Hiếu ở trên.
Nhấp chuột vào đây để mở rộng...
À ý là dùng cái này có bất tiện gì ngoài lý do chậm vì bản chất nó là hàm thì còn nguyên nhân nào nữa mà thấy anh chị ít dùng đó chị.
Vì thấy nó ngắn gọn
 
Upvote 0
HieuCD đã viết:
Mã: 
    For i = 1 To UBound(dArr)
      If thang = dArr(i, 3) and ngay = dArr(i, ?) Then      
        iKey = dArr(i, 1)
        If Len(iKey) > 0 Then
          .Item(iKey) = .Item(iKey) + dArr(i, 4)
        End If      
      End If
    Next i
Nhấp chuột vào đây để mở rộng...
Anh cho em hỏi thêm điều kiện theo từng tháng, từ B1:M1 tương ứng tháng 1 đến tháng 12
thì viết thế nào nhờ anh hỗ trơ để em áp dụng về sau nha
Theo dạng hàm R1C1 thì ta được
Mã: 
  With [B2:M11]
.FormulaR1C1 = "=SUMIFS(SHEET1!RC4,SHEET1!RC1,RC1,SHEET1!RC3,R1C)"
.Value = .Value
End With
End Sub
Em cám ơn

TH2.png
 
Lần chỉnh sửa cuối:
Upvote 0
LamNA đã viết:
À ý là dùng cái này có bất tiện gì ngoài lý do chậm vì bản chất nó là hàm thì còn nguyên nhân nào nữa mà thấy anh chị ít dùng đó chị.
Vì thấy nó ngắn gọn
Nhấp chuột vào đây để mở rộng...
Nếu code viết "hợp lý", code ngắn gọn thường có tốc độ chậm hơn code dài, chọn cách nào tùy tình huống
Dùng công thức của excel thật ra tốc độ cũng rất nhanh, nhưng trong bài nầy cùng 1 "mảng dữ liệu" bị gọi lên rất nhiều lần và xử lý trùng nên bị chậm, code VBA chỉ lấy dữ liệu có 1 lần
Mã: 
Sub Test3()
  Dim Ma(), Thang(), dArr(), Res(), iKey As String, j As Long, i As Long
 
  With Sheets("Sheet2")
    Thang = .Range("B2", .Cells(2, Columns.Count).End(xlToLeft)).Value
    Ma = .Range("A3", .Range("A1000000").End(xlUp)).Value
  End With
  ReDim Res(1 To UBound(Ma), 1 To UBound(Thang, 2))
  With Sheets("Sheet1")
    dArr = .Range("A3", .Range("D1000000").End(xlUp)).Value
  End With
  With CreateObject("scripting.dictionary")
    For i = 1 To UBound(dArr)
      If Len(dArr(i, 1)) > 0 And Len(dArr(i, 3)) > 0 Then
        iKey = dArr(i, 1) & "#" & dArr(i, 3)
        .Item(iKey) = .Item(iKey) + dArr(i, 4)
      End If
    Next i
    For i = 1 To UBound(Ma)
      For j = 1 To UBound(Thang, 2)
        Res(i, j) = .Item(Ma(i, 1) & "#" & Thang(1, j))
      Next j
    Next i
  End With
  Range("B3:D3").Resize(UBound(Res), UBound(Res, 2)) = Res
End Sub
 

File đính kèm

  • Test R1C1.xlsb
    2.6 MB · Đọc: 16
Upvote 0
LamNA đã viết:
Anh chị cho em hỏi ham R1C1 này có điểm yếu gì mà em thấy ít ai sử dụng, đôi lúc áp dụng em thấy nó cũng hay
Em test thử: như vậy có được cho là nhanh không?

- Số dòng tính toán: 1,2323
- Kết quả tính toán 3 cột: 3,693 dòng
- Dữ Liệu DATA: 608,258 dòng
=> kết quả khoảng 92 giây
Nhấp chuột vào đây để mở rộng...

Màu đỏ: ý kiến hay hoặc dở là chủ quan.
- Công thức dài 1 chút thì chỉ một mình bạn hiểu. Khi có tham chiếu tương đối thì khó debug bỏ bố.

Màu xanh: khoan hãy tính đến chuyện nhanh hay chậm.
- Hãy thử với ScreenUpdating False và Calculation xlManual trước đã.
Vả lại, nhanh chậm cũng là tương đối. Ai biết sức máy bạn ra sao. Muốn biết nhanh chậm thì phải tự so sánh với code khác chứ.
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

LuuAnh980
  • Question
Code lấy dữ liệu từ 3 file con.
2 3 4
Trả lời
76
Đọc
1K
ptm0412
ptm0412
P
anh em cho em xin code vba với
Trả lời
0
Đọc
12
phantu93
P
T
Nhờ cả nhà giúp mình về vấn đề hyperlink trong excel
Trả lời
9
Đọc
395
phantu93
P
C
  • Question
Tạo schedule chạy code VBA
Trả lời
0
Đọc
19
castanea
C
M
Cho e hỏi về hàm Subtotal ra kết quả bị lỗi
Trả lời
16
Đọc
15K
bebo021999
B
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom