Làm việc với Range mà không dùng for

[aviaiva]

Ví dụ em có một range [A1:A5]
code vba em có thể viết như sau
' Trường hợp công thức ngắn

with range(

 

[chuot0106]

Ví dụ em có một range [A1:A5]
code vba em có thể viết như sau
' Trường hợp công thức ngắn

with range("a1:a5")

.formular1c1 = "5" 
.value = .value

end with

' Trường hợp công thức quá dài

for i = 1 to 5[INDENT]cells(i,"a") = 5[/INDENT]
next

Cho em hỏi có cách viết code nào phối hợp cả hai thằng trên để tốc độ làm việc tăng lên không vì for i = 1 đến 40.000 dòng sẽ chậm hợn khi viết code dạng with range
Kiểu như

with range("a1:a5")[INDENT]cells(i,"a") = 5[/INDENT]
end with

em xin cảm ơn

Tôi nghĩ bạn áp dụng cách thứ nhất vẫn được mà, tốc độ vẫn nhanh hơn dùng For.

Public Sub test()
Dim i As Long
With Range("a1:a65000")
.FormulaR1C1 = "5"
.Value = .Value
End With
End Sub

 

[aviaiva]

Tôi nghĩ bạn áp dụng cách thứ nhất vẫn được mà, tốc độ vẫn nhanh hơn dùng For.

dạ em cũng hiểu điều đó, nhưng công thức của em quá dài không dùng cách thứ nhất được, viết hết hàng ngang rồi mà vẫn chưa hết (chủ yếu and, or) mà dạng
.formular1c1 =

 

[chuot0106]

dạ em cũng hiểu điều đó, nhưng công thức của em quá dài không dùng cách thứ nhất được, viết hết hàng ngang rồi mà vẫn chưa hết (chủ yếu and, or) mà dạng
.formular1c1 = "" không được phép xuống dòng dưới

Bạn có thể đưa một công thức(ví dụ thôi) của bạn nên được không?

 

[aviaiva]

Bạn có thể đưa một công thức(ví dụ thôi) của bạn nên được không?

Công thức xét học lực của học sinh

=IF(R5=

 

[leonguyenz]

Công thức xét học lực của học sinh

Tôi không nghĩ công thức xếp học lực nó dài như vậy, bạn đưa file tính dăm ba trường hợp để xem có thể rút gọn công thức này không.

 

[chuot0106]

Công thức xét học lực của học sinh

Bạn thử dùng For each xem có nhanh hơn không.

Public Sub test1()
Dim i As Long, t As Double, cll As Range
t = Timer
i = 0
For Each cll In Sheet1.Range("A1:A40000")
cll = 5
Next cll
Debug.Print Timer - t
End Sub

 

[aviaiva]

Tôi không nghĩ công thức xếp học lực nó dài như vậy, bạn đưa file tính dăm ba trường hợp để xem có thể rút gọn công thức này không.

không rút gọn được nữa đâu bác ơi nó là ngắn nhất rồi đó ạ, đó là công thức của bác [URL=

 

[leonguyenz]

không rút gọn được nữa đâu bác ơi nó là ngắn nhất rồi đó ạ, đó là công thức của bác le phuong van
chỉ nhìn vào công thức bác sẽ hiểu được ngành giáo dục giờ nó rối rắm lắm

Bạn thử đưa lên, biêt đâu rút gọn được một chút.
For each 40,000 dòng không nổi đâu.
Giải pháp khác: chèn công thức, .FillDown; .Value = .Value
Ví dụ công thức tại S5.

Sub Button3_Click()
    With Sheet1.Range("S5:S40000")
        .FillDown
        .Value = .Value
    End With
End Sub

 

[huuthang_bd]

.formular1c1 = "" không được phép xuống dòng dưới

Cái này là bạn tự nghĩ hay là bạn đọc ở đâu?
Nói như vậy không đúng nhé.

 

[VetMini]

không rút gọn được nữa đâu bác ơi nó là ngắn nhất rồi đó ạ, đó là công thức của bác le phuong van
chỉ nhìn vào công thức bác sẽ hiểu được ngành giáo dục giờ nó rối rắm lắm

Nó rắc rối ở chỗ người áp dụng ngoan cố không biết cải tiến cho hiệu quả. Nguyên tắc của bảng tính là thiết kế cho khoa học để dễ lập công thức. Mấy ông bà nội này dùng cách vẽ bảng tính xong rồi mới lập công thức đáp ứng nhu cầu. Nhìn cái công thức có biểu thức con lặp đi lặp lại tùm lum {biểu thức COUNTIF(O5:Q5,"CĐ") lặp lại 7 lần} là đủ biết trình độ người lập bảng.

Vả lại, hai trường hợp trên không so sánh được. Khi fill nguyên range, code tự động đổi ô tham chiếu theo thứ tự. Khi fill từng ô, code bắt buộc phải dựa theo luợt vòng lặp để lập công thức.

Cái công thức trên nó chứa 1 đống ký tự tiếng Việt. Bạn có muốn đưa nó vào VBA thì cũng hụt hơi.
Tại sao không chứa nó trong biến
Dim congThuc as String
congThuc = Đọc cell formula hay lập formula ở đây

With Range("a1:a5")
.Formula = congThuc
End With

 

[nghiaphuc]

Công thức xét học lực của học sinh

=IF(R5="","",IF(AND(COUNTIF(O5:Q5,"CĐ")=0,R5>=8,M IN(D5:Q5)>6.4,OR(D5>=8,H5>=8)),"GIỎI",IF(OR(AND(COUNTIF(O5:Q5,"CĐ")=0,R5>=8,MIN(D5:Q5)>3.4,OR (D5>=8,H5>=8),COUNT(D5:Q5)-COUNTIF(D5:Q5,">6.4")=1,COUNTIF(D5:Q5,"<5")=1),AND(COUNTIF(O5:Q5,"CĐ")=0,R5>=6.5,MIN(D5:Q5)>4.9 ,OR(D5>=6.5,H5>=6.5))),"KHÁ",IF(OR(AND(R5>6.4,MIN(D5:Q5)>1.9,OR(D5>6.4,H5>6.4),COUNT (D5:Q5)-COUNTIF(D5:Q5,">=5")=1,COUNTIF(D5:Q5,"<3.5")=1,COU NTIF(O5:Q5,"CĐ")=0),AND(R5>=6.5,MIN(D5:Q5)>4.9,OR (D5>=6.5,H5>=6.5),COUNTIF(O5:Q5,"CĐ")=1),AND(R5>=5,MIN(D5:Q5)>3.4,OR(D5>=5,H5>=5),COUNTIF(O 5:Q5,"CĐ")=0)),"TB",IF(OR(AND(R5>6.4,OR(D5>6.4,H5>6.4),COUNT(D5:Q5)-COUNTIF(D5:Q5,">=5")=1,COUNTIF(D5:Q5,"<2")=1,COUNT IF(O5:Q5,"CĐ")=0),AND(R5>6.4,OR(D5>6.4,H5>6.4),MI N(D5:Q5)>=5,COUNTIF(O5:Q5,"CĐ")=1),AND(R5>3.4,MIN(D5:Q5)>1.9)),"YẾU","KÉM")))))

Thông thường thì tôi làm thế này:
1. Bật công cụ Record Macro lên.
2. Chọn 1 ô (Z5 chẳng hạn, hay đại loại một ô nào đó mà công thức cho kết quả đúng), dán công thức này vào và Enter.
3. Tắt công cụ Record Macro, tôi sẽ có được công thức tương ứng dưới dạng FormulaR1C1. Copy cái đoạn loằng ngoằng này.
4. Vào chỗ viết code, gõ lệnh: [Z5:Z100].FormulaR1C1= và dán cái đoạn vừa copy lúc nãy vào. Vậy là xong.

 

[aviaiva]

Thông thường thì tôi làm thế này:
1. Bật công cụ Record Macro lên.
2. Chọn 1 ô (Z5 chẳng hạn, hay đại loại một ô nào đó mà công thức cho kết quả đúng), dán công thức này vào và Enter.
3. Tắt công cụ Record Macro, tôi sẽ có được công thức tương ứng dưới dạng FormulaR1C1. Copy cái đoạn loằng ngoằng này.
4. Vào chỗ viết code, gõ lệnh: [Z5:Z100].FormulaR1C1= và dán cái đoạn vừa copy lúc nãy vào. Vậy là xong.

dạ em làm thế rồi nhưng mà code dài quá màn hình nên nó bị tràn xuống dòng dưới em thêm dấu _ để nối tiếp dòng mà nó vẫn không được

.formular1c1 =

 

[nghiaphuc]

dạ em làm thế rồi nhưng mà code dài quá màn hình nên nó bị tràn xuống dòng dưới em thêm dấu _ để nối tiếp dòng mà nó vẫn không được

Đường nào thì cũng dùng đến VBA, theo tôi thì bạn nên chuyển qua xếp loại học lực bằng hàm tự tạo có thể vấn đề sẽ đơn giản hơn đấy.
Xin gửi một hàm xếp loại học lực mà tôi đã xây dựng để bạn tham khảo. Tôi viết hàm này cho trường THPT không chuyên nên có thể không phù hợp với trường hợp cụ thể của bạn. Dù sao thì cũng nên tham khảo và nếu có thể thì nhờ bạn và các thầy cô góp ý về hàm này luôn:
[GPECODE=vb]Function HL(Toan As Range, Ly As Range, Hoa As Range, Sinh As Range, Tin As Range, Van As Range, Su As Range, Dia As Range, AV As Range, CN As Range, GDQP As Range, TD As Range, GDCD As Range, DTB As Range) As String
'Ham xep loai hoc luc theo Thong tu 58
Dim CM As Range, MinD As Double, MaxTV As Double, MinDTV As Double, Small2 As Double, Wf

If DTB = "" Then Exit Function
Set Wf = Application.WorksheetFunction
Set CM = Union(Toan, Ly, Hoa, Sinh, Tin, Van, Su, Dia, AV, CN, GDQP, GDCD)
With Wf
MinD = .Min(CM): MaxTV = .Max(Toan, Van): MinDTV = .Min(DTB, MaxTV): Small2 = .Small(CM, 2)
End With
If MinDTV > 7.9 And MinD > 6.4 And TD = ChrW(272) Then 'Gioi - Chuan (Dieu 13.1)
HL = "G"
ElseIf MinDTV > 6.4 And MinD > 4.9 And TD = ChrW(272) Then 'Kha - Chuan (Dieu 13.2)
HL = "K"
ElseIf MinDTV > 7.9 And MinD < 6.5 And MinD > 4.9 And Small2 > 6.4 And TD = ChrW(272) Then 'Kha - Dieu chinh (Dieu 13.6.a)
HL = "K"
ElseIf MinDTV > 4.9 And MinD > 3.4 And TD = ChrW(272) Then 'Tb - Chuan (Dieu 13.3)
HL = "Tb"
ElseIf MinDTV > 7.9 And MinD < 3.5 And MinD > 1.9 And Small2 > 3.4 And TD = ChrW(272) Then 'Tb - Dieu chinh 1 (Dieu 13.6.b)
HL = "Tb"
ElseIf MinDTV > 7.9 And MinD > 3.4 And TD = "C" & ChrW(272) Then 'Tb - Dieu chinh 2 (Dieu 13.6.b)
HL = "Tb"
ElseIf MinDTV > 6.4 And MinD < 3.5 And MinD > 1.9 And Small2 > 4.9 And TD = ChrW(272) Then 'Tb - Dieu chinh 3 (Dieu 13.6.c)
HL = "Tb"
ElseIf MinDTV > 6.4 And MinD > 4.9 And TD = "C" & ChrW(272) Then 'Tb - Dieu chinh 4 (Dieu 13.6.c)
HL = "Tb"
ElseIf DTB > 3.4 And MinD > 1.9 Then 'Yeu - Chuan (Dieu 13.4)
HL = "Y"
ElseIf MinDTV > 6.4 And MinD < 2 And Small2 > 4.9 And TD = ChrW(272) Then 'Yeu - Dieu chinh (Dieu 13.6.d)
HL = "Y"
Else
HL = "Kém"
End If
End Function[/GPECODE]

 

[huuthang_bd]

bác chỉ em cách viết xuống dòng đối với cái này với em không biết

Ví dụ như vầy

[A5:A10].FormulaR1C1 = _
    "=IF(RC[17]="""","""",IF(AND(COUNTIF(RC[14]:RC[16],""CD"")=0,RC[17]>=8,MIN(RC[3]:RC[16])>6.4,OR(RC[3]>=8,RC[7]>=8)),""GIOI"",IF(OR(AND(COUNTIF(RC[14]:RC[16],""CD"")=0,RC[17]>=8,MIN(RC[3]:RC[16])>3.4,OR(RC[3]>=8,RC[7]>=8),COUNT(RC[3]:RC[16])-COUNTIF(RC[3]:RC[16],"">6.4"")=1,COUNTIF(RC[3]:RC[16],""<5"")=1),AND(COUNTIF(RC[14]:RC[16],""CD"")=0,RC[17]>=6.5,MIN(RC[3]:RC[16])>4.9,OR(RC[3]>=6.5,RC[7]>=6.5))),""KHA""," & _
    "IF(OR(AND(RC[17]>6.4,MIN(RC[3]:RC[16])>1.9,OR(RC[3]>6.4,RC[7]>6.4),COUNT(RC[3]:RC[16])-COUNTIF(RC[3]:RC[16],"">=5"")=1,COUNTIF(RC[3]:RC[16],""<3.5"")=1,COUNTIF(RC[14]:RC[16],""CD"")=0),AND(RC[17]>=6.5,MIN(RC[3]:RC[16])>4.9,OR(RC[3]>=6.5,RC[7]>=6.5),COUNTIF(RC[14]:RC[16],""CD"")=1),AND(RC[17]>=5,MIN(RC[3]:RC[16])>3.4,OR(RC[3]>=5,RC[7]>=5),COUNTIF(RC[14]:RC[16],""CD"")=0)),""TB""," & _
    "IF(OR(AND(RC[17]>6.4,OR(RC[3]>6.4,RC[7]>6.4),COUNT(RC[3]:RC[16])-COUNTIF(RC[3]:RC[16],"">=5"")=1,COUNTIF(RC[3]:RC[16],""<2"")=1,COUNTIF(RC[14]:RC[16],""CD"")=0),AND(RC[17]>6.4,OR(RC[3]>6.4,RC[7]>6.4),MIN(RC[3]:RC[16])>=5,COUNTIF(RC[14]:RC[16],""CD"")=1),AND(RC[17]>3.4,MIN(RC[3]:RC[16])>1.9)),""YEU"",""KEM"")))))"

 

[VetMini]

Nếu mục đich của bạn chỉ là lây cái hạng đã xếp thì không cần phải viết code nhiều.

Chỉ cần ghi cái công thức trên vào một ô nào đó, Z1 chẳng hạn.
- Vào cửa số Immediate (Trong VBE, gõ Ctrl+G)
- gõ [A5:A1000].Formula = Cstr([Z1].Formula): [A5:A1000].Value = [A5:A1000].Value
- gõ Enter
(tôi dựa trên code của bạn là bạn muốn chuyển qua số sau khi đã đối chiếu dữ liệu qua công thức. Nếu không muốn làm thế thì bỏ đi phần sau dấu 2 chấm)