Gán công thức trong VBA thành giá trị (1 người xem)

LamNA · 18/8/18

Chào anh chị
Anh chị cho em hỏi với công thức bên dưới làm sao khi chạy công thức xong nó sẽ gán kết quả bằng giá trị Value

Mã:

Sub Test()
    Range("F2").value = "=SUMIFS(B1:B14,A1:A14,E2)"
End Sub

nghiaphuc · 18/8/18

LamNA đã viết:
Chào anh chị
Anh chị cho em hỏi với công thức bên dưới làm sao khi chạy công thức xong nó sẽ gán kết quả bằng giá trị Value

Mã:

Sub Test() Range("F2").value = "=SUMIFS(B1:B14,A1:A14,E2)" End Sub

Nó là thế này:

Mã:

Sub Test()
    Range("F2") = [=SUMIFS(B1:B14,A1:A14,E2)]
End Sub

LamNA · 18/8/18

nghiaphuc đã viết:
Nó là thế này:

Mã:

Sub Test() Range("F2") = [=SUMIFS(B1:B14,A1:A14,E2)] End Sub

Cám ơn anh nhe
Anh cho em hỏi ý nghĩa ý nghĩa của [...] trong VBA là gì vậy anh?

nghiaphuc · 18/8/18

LamNA đã viết:
Cám ơn anh nhe
Anh cho em hỏi ý nghĩa ý nghĩa của [...] trong VBA là gì vậy anh?

Bạn click vào chỗ Evaluate phía dưới chữ ký của tôi, có bài viết của anh Lê Văn Duyệt viết về phương thức này.

LamNA · 18/8/18

nghiaphuc đã viết:
Bạn click vào chỗ Evaluate phía dưới chữ ký của tôi, có bài viết của anh Lê Văn Duyệt viết về phương thức này.

Anh cho em hỏi thêm nếu trường hợp này thì viết nó như thế nào là gọn nhất

Mã:

Sub Test()
    Range("G2") = [=SUMIFS(B1:B14,A1:A14,F2,C1:C14,D2)]
    Range("G3") = [=SUMIFS(B1:B14,A1:A14,F3,C1:C14,D2)]
    Range("G4") = [=SUMIFS(B1:B14,A1:A14,F4,C1:C14,D2)]
End Sub

giaiphap · 18/8/18

LamNA đã viết:
Anh cho em hỏi thêm nếu trường hợp này thì viết nó như thế nào là gọn nhất

Mã:

Sub Test() Range("G2") = [=SUMIFS(B1:B14,A1:A14,F2,C1:C14,D2)] Range("G3") = [=SUMIFS(B1:B14,A1:A14,F3,C1:C14,D2)] Range("G4") = [=SUMIFS(B1:B14,A1:A14,F4,C1:C14,D2)] End Sub

Chắc là thế này.

Mã:

Sub Test()
    Dim i As Integer
    For i = 2 To 4
        Range("G" & i) = Evaluate("SUMIFS(B1:B14,A1:A14,F" & i & ",C1:C14,D2)")
    Next i
End Sub

LamNA · 18/8/18

giaiphap đã viết:
Chắc là thế này.

Mã:

Sub Test() Dim i As Integer For i = 2 To 4 Range("G" & i) = Evaluate("SUMIFS(B1:B14,A1:A14,F" & i & ",C1:C14,D2)") Next i End Sub

Đúng rồi, em cám ơn anh nhe

befaint · 19/8/18

(Trao đổi thôi nhé):
- Không rõ mục đích dùng VBA để viết công thức, chưa kể viết công thức ấy trong code sẽ khó hơn hẳn viết trên bảng tính.
- Về phương diện áp dụng VBA vì cái ưu điểm của nó, nhưng giải pháp ở đây thì không áp dụng được ưu điểm đó.

LamNA · 19/8/18

befaint đã viết:
(Trao đổi thôi nhé):
- Không rõ mục đích dùng VBA để viết công thức, chưa kể viết công thức ấy trong code sẽ khó hơn hẳn viết trên bảng tính.
- Về phương diện áp dụng VBA vì cái ưu điểm của nó, nhưng giải pháp ở đây thì không áp dụng được ưu điểm đó.

Dạ anh, em vọc VBA vài cái cơ bản trước thôi anh, nếu trường hợp này thì dung công thức có sẵn sẽ nhanh và tiện hơn.
Em cám ơn

LamNA · 19/8/18

nghiaphuc đã viết:
Nó là thế này:

Mã:

Sub Test() Range("F2") = [=SUMIFS(B1:B14,A1:A14,E2)] End Sub

Anh Nghĩa cho em hỏi trường hợp công thức sumifs như trong file thì trong VBA mình viết như thế nào là ngắn nhất
Em cám ơn

nghiaphuc · 19/8/18

LamNA đã viết:
Anh Nghĩa cho em hỏi trường hợp công thức sumifs như trong file thì trong VBA mình viết như thế nào là ngắn nhất
Em cám ơn

Ví dụ là thế này:

Mã:

Sub Test2()
    With [F5:AJ10]
        .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)"
        .Value = .Value
    End With
End Sub

VetMini · 19/8/18

Hàm Evaluate là hàm được gọi từ VBA để tính biểu thức (đáng lẽ phải) ở trên Sheet.
Dấu [ ] là cách viết tắt của Evaluate.
Điểm cần để ý là tham của Evaluate là chuỗi cho nên có thể dùng để tính biến, [ ] luôn luôn bắt buộc chỉ dùng hằng.

Chú: hàm Evaluate làm chậm code 1 chút (tuy không đáng kể)
Tôi vẫn thường thắc mắc tại sao quý vị ở đây rất chú trọng về tốc độ nhưng lại thích viết [A1] thay vì Range("A1"). Nhưng hình như luật ở đây điều kiện viết code ngắn đứng trên điều kiện tốc độ.

nghiaphuc · 19/8/18

VetMini đã viết:
Chú: hàm Evaluate làm chậm code 1 chút (tuy không đáng kể)
Tôi vẫn thường thắc mắc tại sao quý vị ở đây rất chú trọng về tốc độ nhưng lại thích viết [A1] thay vì Range("A1"). Nhưng hình như luật ở đây điều kiện viết code ngắn đứng trên điều kiện tốc độ.

Có lẽ là do thói quen thôi anh, chứ như em chẳng hạn thì cũng không nhận ra sự khác biệt về tốc độ giữa 2 cách viết này, với em thì code chạy được là ngon rồi, hay dở tính sau. Bình thường em hay viết [A1], nhưng đôi khi cần sự gợi ý của VBE (khi gõ dấu chấm sau đối tượng) thì em viết Range("A1").

VetMini · 20/8/18

Nói cho đúng thì chỉ hơi chậm 1 chút. Tôi nhớ đọc ở đâu đó có người tính ra được là khoảng 10%. 10% của con toán lấy range thì hầu như không ảnh hưởng gì đến code, còn hằng trăm con toán khác.
Vấn đề quan trọng là do ký hiệu [ ] không thể xử dụng với biến cho nên nếu code có những con toán khác về range thì đọc sẽ thấy không đồng nhất, gạo nếp lẫn lộn. Lúc thì [A1], lúc lại Range("A" & i).
Bản thân tôi cũng có dùng [ ]. Nhưng tôi chỉ dùng trong 2 trường hợp:
1. Nếu dùng để thay Evaluate Range thì chỉ dùng trong đoạn code rất ngắn. Cái Sub hay Function chỉ mươi dòng là tối đa.
2. Trường hợp thứ hai là dùng để tạo một mảng. Mảng này là mảng theo ngữ cảnh worksheet (*) cho nên chỉ số LBound là 1, khác với mảng tạo bởi hàm Array.

(*) Evaluate không phải là hàm của worksheet, nhưng nó hoạt động theo ngữ cảnh worksheet.

khi ta 20 · 20/8/18

giaiphap đã viết:
Chắc là thế này.

Mã:

Sub Test() Dim i As Integer For i = 2 To 4 Range("G" & i) = Evaluate("SUMIFS(B1:B14,A1:A14,F" & i & ",C1:C14,D2)") Next i End Sub

Quá cần thiết cho những người muốn biết về VBA như em.Cám ơn anh nhiều.
Em lấy có value, value2 .Vậy những value này thì được dùng trong trường hợp nào vậy anh ?

befaint · 20/8/18

khi ta 20 đã viết:
có value, value2 .Vậy những value này thì được dùng trong trường hợp nào

Có value3, value4, value5 nữa đó bạn.

Tìm mục 3.1 đọc thử bạn nhé.

khi ta 20 · 20/8/18

befaint đã viết:
Có value3, value4, value5 nữa đó bạn.

Tìm mục 3.1 đọc thử bạn nhé.

Thiệt hôn đó hay là chọc em vậy anh ?Úm ba la, cám ơn anh nha

giaiphap · 20/8/18

khi ta 20 đã viết:
Thiệt hôn đó hay là chọc em vậy anh ?Úm ba la, cám ơn anh nha

Muốn biết chọc hay không vào Link trên sẽ rõ thôi.

khi ta 20 · 20/8/18

giaiphap đã viết:
Muốn biết chọc hay không vào Link trên sẽ rõ thôi.

Em thấy rồi anh. Mà value3,value4,value5 đâu ta ?

giaiphap · 20/8/18

khi ta 20 đã viết:
Em thấy rồi anh. Mà value3,value4,value5 đâu ta ?
View attachment 202186

Vậy có kết luận luôn đi, hỏi gì nửa.

khi ta 20 · 20/8/18

giaiphap đã viết:
Vậy có kết luận luôn đi, hỏi gì nửa.

Cám ơn anh.
Hừ hừ, vậy là 2 anh ý khác nhau chổ ngày tháng(date).
À mà em còn có chổ này nữa muốn hỏi. Em muốn copy mấy bài viết của mọi người về word để in ra xem.Nhưng sau phần chữ thì được, mà hình ảnh thì không được ?

LamNA · 21/8/18

nghiaphuc đã viết:
Ví dụ là thế này:

Mã:

Sub Test2() With [F5:AJ10] .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)" .Value = .Value End With End Sub

Anh cho em hỏi thêm nếu trong trường hợp các cột không liên tiếp thì mình viết thế nào để chạy kết quả đúng
Em cám ơn

Mã:

Sub Test2()
    With [F5:AJ10,H5:H10.........] 'Em thử như vậy kết quả không chính xác

        .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)"

        .Value = .Value

    End With
End Sub

nghiaphuc · 21/8/18

LamNA đã viết:
Anh cho em hỏi thêm nếu trong trường hợp các cột không liên tiếp thì mình viết thế nào để chạy kết quả đúng
Em cám ơn

Mã:

Sub Test2() With [F5:AJ10,H5:H10.........] 'Em thử như vậy kết quả không chính xác .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)" .Value = .Value End With End Sub

Thử thế này xem, với 31 vùng như vậy thì đưa vào cái range kia dài dòng lắm:

Mã:

Sub Test3()
    Dim Cll As Range
    For Each Cll In Sheet1.[F4:BN4].SpecialCells(xlCellTypeConstants)
        With Cll.Offset(1).Resize(6)
            .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)"
            .Value = .Value
        End With
    Next
End Sub

phulien1902 · 21/8/18

LamNA đã viết:
Anh cho em hỏi thêm nếu trong trường hợp các cột không liên tiếp thì mình viết thế nào để chạy kết quả đúng
Em cám ơn

Mã:

Sub Test2() With [F5:AJ10,H5:H10.........] 'Em thử như vậy kết quả không chính xác .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)" .Value = .Value End With End Sub

1 cách:

PHP:

Sub abc()
    Dim i&, j%, LR&, LC%
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Range("F5:F" & LR).Formula = "= SUMIFS($B:$B,$A:$A,$E5,$C:$C,F4)"
    For i = 5 To LR
        For j = 6 To 66 Step 2
            Cells(i, 6).Copy Cells(i, j)
        Next
    Next
End Sub

LamNA · 21/8/18

nghiaphuc đã viết:
Thử thế này xem, với 31 vùng như vậy thì đưa vào cái range kia dài dòng lắm:

Mã:

Sub Test3() Dim Cll As Range For Each Cll In Sheet1.[F4:BN4].SpecialCells(xlCellTypeConstants) With Cll.Offset(1).Resize(6) .FormulaR1C1 = "=SUMIFS(C2,C1,RC5,C3,R4C)" .Value = .Value End With Next End Sub

Cám ơn anh Nghĩa
Chạy kết quả nhanh
Theo code em hiểu đoạn SpecialCells(xlCellTypeConstants) là công thức chỉ tính các ô có chứa hằng số, các ô trống bỏ qua không tính
Nó sẽ phát sinh tính toán công thức khi các ô trống có chứa giá trị.

phulien1902 đã viết:

1 cách:

PHP:

Sub abc()
    Dim i&, j%, LR&, LC%
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Range("F5:F" & LR).Formula = "= SUMIFS($B:$B,$A:$A,$E5,$C:$C,F4)"
      For i = 5 To LR
        For j = 6 To 66 Step 2
           Cells(i, 6).Copy Cells(i, j)
        Next
    Next
End Sub

Cám ơn anh phulien1902
Tính toán chậm do chạy lệnh For
Code của anh theo em hiểu là chạy tính toán vòng lặp từ F6 đến BN và bỏ cách nhau 1 dòng
Nó sẽ phát sinh tính toán công thức với dữ liệu trên 1000 dòng có thể phát sinh có kết quả chậm.

Em cám ơn 2 anh đã quan tâm bài viết.

nghiaphuc · 21/8/18

phulien1902 đã viết:

1 cách:

PHP:

Sub abc()
    Dim i&, j%, LR&, LC%
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Range("F5:F" & LR).Formula = "= SUMIFS($B:$B,$A:$A,$E5,$C:$C,F4)"
      For i = 5 To LR
        For j = 6 To 66 Step 2
           Cells(i, 6).Copy Cells(i, j)
        Next
    Next
End Sub

Cái chỗ F4 không ổn rồi anh, phải là F$4 mới đúng.
Giữ ý tưởng đó, em sửa lại thế này, sẽ nhanh hơn chút vì giảm đi số lần copy:

Mã:

Sub defghij()
    Dim j%, LR&, LC%
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    Range("F5:F" & LR).Formula = "=SUMIFS($B:$B,$A:$A,$E5,$C:$C,F$4)"
    For j = 8 To 66 Step 2
        Cells(5, 6).Resize(LR - 4).Copy Cells(5, j)
    Next
End Sub

Bài đã được tự động gộp: 21/8/18

LamNA đã viết:
Cám ơn anh Nghĩa
Chạy kết quả nhanh
Theo code em hiểu đoạn SpecialCells(xlCellTypeConstants) là công thức chỉ tính các ô có chứa hằng số, các ô trống bỏ qua không tính
Nó sẽ phát sinh tính toán công thức khi các ô trống có chứa giá trị.

Nếu các cột cách đều thì dùng vòng For với Step là khoảng cách giữa các cột chứa công thức. Nói chung là chế biến cái này dễ mà.

Đính chính: Mình tên là Phúc (Hoàng Nghĩa Phúc) chứ không phải Nghĩa.

LamNA · 21/8/18

nghiaphuc đã viết:
Cái chỗ F4 không ổn rồi anh, phải là F$4 mới đúng.
Giữ ý tưởng đó, em sửa lại thế này, sẽ nhanh hơn chút vì giảm đi số lần copy:

Mã:

Sub defghij() Dim j%, LR&, LC% LR = Cells(Rows.Count, "E").End(xlUp).Row Range("F5:F" & LR).Formula = "=SUMIFS($B:$B,$A:$A,$E5,$C:$C,F$4)" For j = 8 To 66 Step 2 Cells(5, 6).Resize(LR - 4).Copy Cells(5, j) Next End Sub

Bài đã được tự động gộp: 21/8/18

Nếu các cột cách đều thì dùng vòng For với Step là khoảng cách giữa các cột chứa công thức. Nói chung là chế biến cái này dễ mà.

Đính chính: Mình tên là Phúc (Hoàng Nghĩa Phúc) chứ không phải Nghĩa.

Dạ em cám ơn anh Phúc nhe, thấy bài toán cũng đã được giải quyết

Bài đã được tự động gộp: 21/8/18

nghiaphuc đã viết:
Cái chỗ F4 không ổn rồi anh, phải là F$4 mới đúng.
Giữ ý tưởng đó, em sửa lại thế này, sẽ nhanh hơn chút vì giảm đi số lần copy:

Mã:

Sub defghij() Dim j%, LR&, LC% LR = Cells(Rows.Count, "E").End(xlUp).Row Range("F5:F" & LR).Formula = "=SUMIFS($B:$B,$A:$A,$E5,$C:$C,F$4)" For j = 8 To 66 Step 2 Cells(5, 6).Resize(LR - 4).Copy Cells(5, j) Next End Sub

Bài đã được tự động gộp: 21/8/18

Nếu các cột cách đều thì dùng vòng For với Step là khoảng cách giữa các cột chứa công thức. Nói chung là chế biến cái này dễ mà.

Đính chính: Mình tên là Phúc (Hoàng Nghĩa Phúc) chứ không phải Nghĩa.

Anh cho em hỏi thêm ý nghĩa của dấu % của 2 biến này nhe

Mã:

Dim j%, LC%

nghiaphuc · 21/8/18

LamNA đã viết:
Anh cho em hỏi thêm ý nghĩa của dấu % của 2 biến này nhe

Mã:

Dim j%, LC%

À, hình như j% tương ứng với j As Integer, LR& tương ứng với LR As Long, d# tương ứng với d As Double. Mình không dùng cách khai báo biến kiểu này nên không rành.

Gán công thức trong VBA thành giá trị (1 người xem)

Người dùng đang xem chủ đề này

Thành viên tích cực

File đính kèm

Thành viên gạo cội

Thành viên tích cực

Thành viên gạo cội

Thành viên tích cực

File đính kèm

==(^o^)==

Thành viên tích cực

|||||||||||||

Thành viên tích cực

Thành viên tích cực

File đính kèm

Thành viên gạo cội

Đang đi tìm hòn đá

Thành viên gạo cội

Đang đi tìm hòn đá

Thành viên thường trực

|||||||||||||

Thành viên thường trực

==(^o^)==

Thành viên thường trực

==(^o^)==

Thành viên thường trực

Thành viên tích cực

File đính kèm

Thành viên gạo cội

GPE - My love

Thành viên tích cực

Thành viên gạo cội

Thành viên tích cực

Thành viên gạo cội

Thời gian đếm ngược.