Hỏi về tham chiếu cells trong VBA (1 người xem)

[embevinhphuc]

Chào các anh chị và các thầy cô, em mới chập chững học VBA. Mạo muội xin hỏi điều ngu ngốc dưới đây, Kính mong được giúp đỡ. Chân thành cảm ơn anh chị và các thầy cô.

Ví dụ trong file đính kèm là một bài tập copy các cột theo điều kiện ở cells (1,i), trong file này em sử dụng tham chiếu cells để tính, khi mở rộng dữ liệu ra nhiều nghìn dòng+cột thì chạy quá lâu. em có đọc trên GPE là dùng range và mảng thì tốc độ code nhanh hơn nhiều, nhưng hiện tại em chưa hiểu và dùng đk range và mảng. Vì vậy kính nhờ các thầy cô anh chị chuyển code thành code tham chiếu range và mảng ạ (2 loại riêng).
PHP code
Sub hocvba()
Dim i As Long
Dim j As Long
Dim k As Long
Worksheets("2").Activate
For i = 1 To 15
For j = 19 To 47
For k = 4 To 500
If Cells(1, j).Value = Cells(1, i) Then
Cells(k, j).Value = Cells(k, i).Value
Else
End If
Next
Next
Next

End Sub

 

[VetMini]

Việc "dùng range và mảng thì tốc độ code nhanh hơn nhiều" là lĩnh vực của các chuyên viên về tốc độ ở đây, tôi không dám rớ vào.

Riêng về thuật toán thì tôi có thể nói. Bài của bạn có một lỗi rất nặng về thuật toán.

Cái vòng lặp này:

For k = 4 To 500
If Cells(1, j).Value = Cells(1, i) Then
Cells(k, j).Value = Cells(k, i).Value
Else
End If
Next

Nó lặp lại đúng 497 lần in hệt một cái công việc mà đáng lẽ nó chỉ cần làm 1 lần.

Nếu là tôi, tôi học về thuật toán trước khi nói chuyện về các kỹ xảo tốc độ. Tuy nhiên, tôi không phải là thánh. Nhiều người ở đây chuyên về tốc độ và viết code rất chiến (chiến hơn tôi nhiều). Việc học là việc của bạn.

 

[dhn46]

Đúng như bác VetMini đã nói, với cái vòng lặp k của bạn thì bạn có thể thay bằng việc copy value từ vùng dữ liệu gốc sang vùng dữ liệu đích thì sẽ hiệu quả hơn, chỉ làm có đúng 1 lần.

Với dữ liệu copy passte đơn thuần như thế này thì dùng range cũng không quá chậm mà lại dễ hiểu nữa, khi nào bạn thành thạo range thì hãy chuyển qua mảng, mảng thực chất cũng có cấu trúc dữ như range song nó không hiển thì trực tiếp mà phải tưởng tượng và nó chỉ gồm các dữ liệu không bao gồm định dạng, hiển thị...nên tốc độ nhanh.

Trong code bạn thêm các dòng code như dưới đây để cải thiện tốc độ

Sub Test()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual


    'Viet Code o day
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[ndu96081631]

Chào các anh chị và các thầy cô, em mới chập chững học VBA. Mạo muội xin hỏi điều ngu ngốc dưới đây, Kính mong được giúp đỡ. Chân thành cảm ơn anh chị và các thầy cô.

Ví dụ trong file đính kèm là một bài tập copy các cột theo điều kiện ở cells (1,i), trong file này em sử dụng tham chiếu cells để tính, khi mở rộng dữ liệu ra nhiều nghìn dòng+cột thì chạy quá lâu. em có đọc trên GPE là dùng range và mảng thì tốc độ code nhanh hơn nhiều, nhưng hiện tại em chưa hiểu và dùng đk range và mảng. Vì vậy kính nhờ các thầy cô anh chị chuyển code thành code tham chiếu range và mảng ạ (2 loại riêng).
PHP code
Sub hocvba()
Dim i As Long
Dim j As Long
Dim k As Long
Worksheets("2").Activate
For i = 1 To 15
For j = 19 To 47
For k = 4 To 500
If Cells(1, j).Value = Cells(1, i) Then
Cells(k, j).Value = Cells(k, i).Value
Else
End If
Next
Next
Next

End Sub

Bài này có rất nhiều cách làm. Dùng Find Method cũng ngắn gọn và dễ hiểu đây:

Sub hocvba()
  Dim rng1 As Range, rng2 As Range, rFind As Range, cel As Range
  Dim wks As Worksheet
  Set wks = Worksheets("2")
  Set rng1 = wks.Range("A1:O1")
  Set rng2 = wks.Range("S1:AU1")
  For Each cel In rng2
    Set rFind = rng1.Find(cel.Value, , xlValues, xlWhole)
    If Not rFind Is Nothing Then
      cel.Offset(3).Resize(497).Value = rFind.Offset(3).Resize(497).Value
    End If
  Next
  MsgBox "Xong phim!"
End Sub

 

[embevinhphuc]

Em cảm ơn các anh chị và thầy. Chính xác đây là code thứ 2 em viết mà nó chạy đk. code đầu e đọc và làm theo đk bài advance filter. em đã dùng cells là vì ban đầu em không biết gán biến thành cells như thế nào, sau mấy ngày đọc ở GPE thì cuối cùng em đã biết có cái cells(i,j). E sẽ check code của các anh và thầy để hiểu biết thêm ạ. Rất mong được giúp đỡ, em chân thành cảm ơn nhiều.

 

[embevinhphuc]

Haha, e chỉ cần đổi thành code sau thì bỏ đk vòng lặp k, nhanh hơn hàng trăm nghìn lần. Tại vì em không biết khai báo vòng lặp với range.
Code của thầy ndu96081631 nhanh nhưng giở em chưa hiểu đk, chắc phải ngâm cứu nhiều.
Sub HoanVi()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim i As Long
Dim j As Long


Worksheets("HoanVi").Activate
For i = 6 To 32
For j = 34 To 1517

If Cells(1, j).Value = Cells(1, i) Then
Range(Cells(10, j), Cells(4010, j)).Value = Range(Cells(10, i), Cells(4010, i)).Value

Else
End If

Next
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ndu96081631]

Haha, e chỉ cần đổi thành code sau thì bỏ đk vòng lặp k, nhanh hơn hàng trăm nghìn lần. Tại vì em không biết khai báo vòng lặp với range.
Code của thầy ndu96081631 nhanh nhưng giở em chưa hiểu đk, chắc phải ngâm cứu nhiều.
Sub HoanVi()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim i As Long
Dim j As Long


Worksheets("HoanVi").Activate
For i = 6 To 32
For j = 34 To 1517

If Cells(1, j).Value = Cells(1, i) Then
Range(Cells(10, j), Cells(4010, j)).Value = Range(Cells(10, i), Cells(4010, i)).Value

Else
End If

Next
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Nên ghi rõ tên sheet trước Range("...") hoặc Cells(...), như vậy đở mất công thêm công đoạn Worksheets("HoanVi").Activate
----------------------

Code của thầy ndu96081631 nhanh nhưng giở em chưa hiểu đk, chắc phải ngâm cứu nhiều.

Chỉ là FIND thôi mà, giống như bạn bấm Ctrl + F rồi tìm bằng tay vậy thôi!

 

[ninh_lu]

Dear Thầy và cácbạn.

Em có đoạn codesau chạy tham chiếu giữa 2 workbooks…Và tham chiếu theo Range như “embevinhphuc” có sửdụng. Nhưng không hiểu sao chạy không được và nó báo lỗi “ Application-defined orobject define error “ở dòng code bên dưới mà em có Highlight màu đỏ đậm

Rấtmong Thầy và các bạn coi giùm ạ.
Emxin chan thanh cảm ơn!


“OptionExplicit
SubPCMA_Button1_Click()
Dimi As Integer
Dimj As Integer
Dima21 As Integer

Dimwbk As Workbook '

Dimresult As String

result = InputBox("Pls kindly inputPassword..!", "Authentication")

If result = "112233" Then

WithApplication
.Interactive= False
.EnableEvents= False
.ScreenUpdating= False
.Calculation= xlCalculationManual
EndWith

Fori = 5 To 30 ' so luong nguoi trong 1 bo phan
Forj = 17 To 22

'IfSheet1.Cells(i, 2) = Workbooks("OVT.xlsm").Sheets(1).Cells(k, 2) Then
'Workbooks("OVT.xlsm").Sheets(1).Cells(k,2).Font.Color = vbGreen
'EndIf

IfSheet1.Cells(2, 1) = "21" Then
Fora21 = 6 To 11
If Sheet1.Cells(i,2).Text = Workbooks("OVT.xlsm").Sheets(1).Range(Cells(8, 2),Cells(700, 2)) Then
IfSheet1.Cells(4, j).Text = Workbooks("OVT.xlsm").Sheets(1).Cells(7,a21) Then
Workbooks("OVT.xlsm").Sheets(1).Range(Cells(8,a21), Cells(700, a21)) = Sheet1.Cells(i, j).Text
EndIf
EndIf
Nexta21
EndIf

Nextj
Nexti

WithApplication
.Interactive= True
.EnableEvents= True
.ScreenUpdating= True
.Calculation= xlCalculationManual
EndWith

Else
MsgBox " Invalid Password..."
EndIf

EndSub
“