Sử dụng công thức có sẵn của Excel trong VB-Gán công thức SUMPRODUCT -Hỏi về CASE ...

vanphuc1701 · 12/7/07

MONG MỌI NGƯỜI GIÚP DÙM. CẢM ƠN NHIỀU NHIỀU!!
Hỏi về sử dụng công thức có sẵn của Excel trong VB??? Cảm ơn các cao thủ đã quan tâm.Mình đang tập tành học VB nên không đủ sức giải quyết các vấn đề này :

Vấn đề 1a :
Giả sử từ E8:E1000 có 75 dòng dữ liệu. Nhưng A5 =1????; B5=75 . Sửa lại thế nào để ko cần “mangdem" vẫn có đáp số đúng.

Mã:

[FONT=Times New Roman][SIZE=3]Sub Vidu()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim mangdem As Range[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set mangdem = ActiveSheet.Range("E8:E1000")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA("E8:E1000")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Range("B5").Value = ActiveSheet.Application.WorksheetFunction.CountA(mangdem)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

Đáp số : A5 =1 không đúng ý mong đợi ; B5=75 đúng ý .
Tại sao A5 =1. Sửa lại thế nào để ko cần thông qua “mangdem" nhưng đáp số B5 vẫn là 75 không ???.

Vấn đề 1b: Số liệu có thể bố trí ở khắp từ (“Asốdòng:Xsốdòng” )
Hàm hay code nào để có được số dòng cuối cùng của bảng tính. Xác định được đáp số : sốdòng ?????

Vấn đề 2 : Gán công thức SUMPRODUCT cho một CELL với một biến đếm được tính từ công thức có sẵn của Excel. Đoạn code lỗi ở đâu tại sao CELL có công thức SUMPRODUCT không đúng : =SUMPRODUCT(('E8':'E16'>10)*('E8':'E16'<=18)*('X8':'X16'=A10)*'P8':'P16') ???????

Mã:

[FONT=Times New Roman][SIZE=3]Dim ChiuThua As Integer[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim mangdem As Range[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set mangdem = ActiveSheet.Range("E8:E1000")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]............................[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]............................[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]ChiuThua = ActiveSheet.Application.WorksheetFunction.CountA(mangdem) + 7[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Range("S100").Value = "=SUMPRODUCT((E8:E" & ChiuThua & ">18)*(X8:X" & ChiuThua & "=RC[-18])*P8:P" & ChiuThua & ")"[/SIZE][/FONT]

Vấn đề 3a : Application.Run "sub nào đó" khác Run "sub nào đó" như thế nào? Cái nào chạy nhanh hơn
Vấn đề 3b : Cao thủ nào đó cho mình xin đoạn code để ghép vào đoạn code khác đếm thời gian chạy nhằm so sánh tính nhanh chậm của các đoạn code

Vấn đề 3c : vidu() và Test() cái nào chạy nhanh hơn ???????
Sub Vidu()
Đoạn code để Thi hành nhóm lệnh 1
Đoạn code để Thi hành nhóm lệnh 2
Đoạn code để Thi hành nhóm lệnh 3
Đoạn code để Thi hành nhóm lệnh 4
Đoạn code để Thi hành nhóm lệnh 5
Đoạn code để Thi hành nhóm lệnh 6
End Sub

Sub Test()
Application.Run “vidu1”
Application.Run “vidu2”
Application.Run “vidu3”
End Sub
với
Sub Vidu1() ‘: gom nhóm lệnh 1-2 lại
Thi hành lệnh 1
Thi hành lệnh 2
End Sub
Sub Vidu2() ‘: gom nhóm lệnh 3-4 lại
Thi hành lệnh 3
Thi hành lệnh 4
End Sub
Sub Vidu3() ‘: gom nhóm lệnh 5-6 lại
Thi hành lệnh 5
Thi hành lệnh 6
End Sub

Vấn đề 4: Hỏi về CASE ?????????????
‘Kiểm tra giá trị cột AA để gán công thức cho cột Z
DangCongThuc = Range("AA" & i).Value
Select Case CongThuc
Case "a+b+c+d+e"
Range("Z" & i).Value = "=sum(F" & i & ":J" & i & ")"
Case "4*a+2*b"
Range("Z" & i).Value = "=4*F" & i & "+2*G" & i & ""
Case …….
……………
‘Hỏi : Thêm đoạn code nào để nếu không thỏa mãn các “CASE” trên thì thực hiện : ?????????????
'Range("Z" & i).Value = "NOTE : ch" & ChrW(432) & "a có cách tính chi" & ChrW(7873) & "u dài"
End Select

Vấn đề 5 : giải thích đoạn code này dùm

Mã:

[SIZE=3][FONT=Times New Roman]ThongKe = ThongKe & IIf(InStr(1, ThongKe, Right("00" & Application.Range("E" & i).Value, 2)) = 0, "," & Right("00" & Application.Range("E" & i).Value, 2), "")[/FONT][/SIZE]

Vấn đề 6 : khống chế dấu “,” và dấu “;” . Mình tạo code để FormatConditions nhưng khi đi máy khác nó sẽ hiểu sai dấu “,” và “;” do định dạng mỗi máy khác nhau vì thế phải vào sửa lại code. Đoạn code nào để khống chế điều này .
Range("B8" & ":AA" & SoDong).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A8<>"""""
Selection.FormatConditions(1).Borders(xlTop).Weight = xlThin
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A8="""",$E8<>0)" '???????LÀM SAO KHỐNG CHẾ DẤU “;” VÀ “,” nhiều ĐOẠN CODE KHÁC ĐỀU BỊ NHƯ VẬY.
Selection.FormatConditions(2).Borders(xlTop).Weight = xlHairline
Selection.FormatConditions(2).Borders(xlBottom).Weight = xlHairline

Vấn đề 7 : tạo DEFINAME
Hỏi : Mình có sheet1, sheet2,…. Mình muốn tạo definename gồm các tên sau đây : MangA, MangB, MangC. Muốn mở sheet mới, chạy lệnh thì đều tạo được các definename này, TẤT CẢ CÁC SHEET KHI THỰC HIỆN LỆNH ĐỀU CÓ THỂ TẠO CÁC DEFINENAME TRÙNG TÊN, PHỤ THUỘC VÀO DỮ LIỆU CỘT 5 CỦA CÁC SHEET. Code hiện nay của mình tạo definename ở sheet1, qua sheet2 tạo thì sheet1 mất, phải làm sao?

ActiveWorkbook.Names.Add Name:="MangA", RefersToR1C1:="=OFFSET(R8C23,0,0,COUNTA(R8C5:R1000C5),1)"
ActiveWorkbook.Names.Add Name:="MangB", RefersToR1C1:="=OFFSET(R8C16,0,0,COUNTA(R8C5:R1000C5),1)"
ActiveWorkbook.Names.Add Name:="MangC", RefersToR1C1:="=OFFSET(R8C5,0,0,COUNTA(R8C5:R1000C5),1)"

Vấn đề 8 : Hỏi về ComboBox
Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1
End Sub
Sửa lại thế nào để ComboBox chỉ thực thi trong cột A của Sheet : NKC thôi ?????

chibi · 12/7/07

Có nhiều vấn đề bạn nêu đã có trên diễn đàn,bạn chịu khó tìm một chút.
Món lẩu thập cẩm này khó "nhai" quá, hãy chia nhỏ ra để dễ thảo luận.

vanphuc1701 · 12/7/07

chibi đã viết:
Có nhiều vấn đề bạn nêu đã có trên diễn đàn,bạn chịu khó tìm một chút.
Món lẩu thập cẩm này khó "nhai" quá, hãy chia nhỏ ra để dễ thảo luận.

Mình đã tìm hiểu và tham khảo gần hết diễn đàn rồi.(download và lưu lại). Mấy vấn đề mình đưa ra có nhiều phần mình cũng học từ diễn đàn và làm sử dụng theo ý mình nhưng có vài chổ không phát triển được nên lại hỏi tiếp. Ai giúp được phần nào thì giúp. Cảm ơn nhiều. Hihi, VB mình toàn đi học lóm thôi, chưa vô trường lớp gì cả nên có thể có nhiều cây hỏi chắc là ngốc lắm.

Mr Okebab · 12/7/07

quá nhiều để đọc (huống chi là giải đáp). Vì vậy bạn nên chia nhỏ ra.
Mình mạn phép trả lời từ từ nhé .

Vấn đề 1a :
Giả sử từ E8:E1000 có 75 dòng dữ liệu. Nhưng A5 =1????; B5=75 . Sửa lại thế nào để ko cần “mangdem" vẫn có đáp số đúng.
Sub Vidu()
Dim mangdem As Range
Set mangdem = ActiveSheet.Range("E8:E1000")
Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA(" E8:E1000")
Range("B5").Value = ActiveSheet.Application.WorksheetFunction.CountA(m angdem)
End Sub
Đáp số : A5 =1 không đúng ý mong đợi ; B5=75 đúng ý .
Tại sao A5 =1. Sửa lại thế nào để ko cần thông qua “mangdem" nhưng đáp số B5 vẫn là 75 không ???.

chắc cái này bạn phải gửi file lên thì mới kiểm chứng được.

Vấn đề 1b: Số liệu có thể bố trí ở khắp từ (“Asốdòng:Xsốdòng” )
Hàm hay code nào để có được số dòng cuối cùng của bảng tính. Xác định được đáp số : sốdòng ?????

Bạn thử nhé :

Mã:

 Option Explicit

Sub tam()
    Dim i As Byte
    Dim DongCuoi As Long
    
    For i = 1 To 24
        If Cells(65500, i).End(xlUp).Row > DongCuoi Then DongCuoi = Cells(65500, i).End(xlUp).Row
    Next


End Sub

Vấn đề 2 : Gán công thức SUMPRODUCT cho một CELL với một biến đếm được tính từ công thức có sẵn của Excel. Đoạn code lỗi ở đâu tại sao CELL có công thức SUMPRODUCT không đúng : =SUMPRODUCT(('E8':'E16'>10)*('E8':'E16'<=18)*('X8' :'X16'=A10)*'P8':'P16') ???????

Dim ChiuThua As Integer
Dim mangdem As Range
Set mangdem = ActiveSheet.Range("E8:E1000")
............................
............................
ChiuThua = ActiveSheet.Application.WorksheetFunction.CountA(m angdem) + 7

Range("S100").Value = "=SUMPRODUCT((E8:E" & ChiuThua & ">18)*(X8:X" & ChiuThua & "=RC[-18])*P8" & ChiuThua & ")"

Trước hết :
Range("S100").Value = "=SUMPRODUCT((E8:E" & ChiuThua & ">18)*(X8:X" & ChiuThua & "=RC[-18])*P8

" & ChiuThua & ")"

Đây là bạn gán giá trị c(là 1 chuỗi ) chứ không phải là gán công thức.
Bạn nên tham khảo bài viết này, đặc biệt bài số 7 của Đào Việt Cường thì sẽ rõ hơn.
http://www.giaiphapexcel.com/forum/showthread.php?t=816

Còn nếu muốn là công thức thì không thể nửa nạc nửa mỡ như thế được.
Vừa RC vừa E:E, vừa excel vừa VBA, làm sao mà nó hiểu được
- RC : "=RC[-18])
- Non RC : (E8:E"
- Excel : = "=SUMPRODUCT((E8:E"
- VBA : (E8:E" & ChiuThua & ">18)
Bạn chịu khó dùng Macro để xem nó VBA thể hiện 1 hàm như thế nào nhé.

Vấn đề 4: Hỏi về CASE ?????????????
‘Kiểm tra giá trị cột AA để gán công thức cho cột Z
DangCongThuc = Range("AA" & i).Value
Select Case CongThuc
Case "a+b+c+d+e"
Range("Z" & i).Value = "=sum(F" & i & ":J" & i & ")"
Case "4*a+2*b"
Range("Z" & i).Value = "=4*F" & i & "+2*G" & i & ""
Case …….
……………
‘Hỏi : Thêm đoạn code nào để nếu không thỏa mãn các “CASE” trên thì thực hiện : ?????????????
'Range("Z" & i).Value = "NOTE : ch" & ChrW(432) & "a có cách tính chi" & ChrW(7873) & "u dài"
End Select

bạn chỉ cần thêm Case else ở sau cùng là được

Mr Okebab · 12/7/07

Vấn đề 5 : giải thích đoạn code này dùm

ThongKe = ThongKe & IIf(InStr(1, ThongKe, Right("00" & Application.Range("E" & i).Value, 2)) = 0, "," & Right("00" & Application.Range("E" & i).Value, 2), "")

Không cần đi vào chi tiết, chỉ cần nói thêm cho bạn hiểu (vì mình chắc rằng bạn cũng rất khá về excel)

-hàm IIf : Giống hàm if của excel
- hàm : InStr : Giống hàm Find của Excel.

Còn cái application cho vào để đủ mâm thôi mà.

Vậy là bạn có thể hình dung ra rồi phải không ??

Vấn đề 6 : khống chế dấu “,” và dấu “;” . Mình tạo code để FormatConditions nhưng khi đi máy khác nó sẽ hiểu sai dấu “,” và “;” do định dạng mỗi máy khác nhau vì thế phải vào sửa lại code. Đoạn code nào để khống chế điều này .
Range("B8" & ":AA" & SoDong).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A8<>"""""
Selection.FormatConditions(1).Borders(xlTop).Weigh t = xlThin
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($A8="""",$E8<>0)" '???????LÀM SAO KHỐNG CHẾ DẤU “;” VÀ “,” nhiều ĐOẠN CODE KHÁC ĐỀU BỊ NHƯ VẬY.
Selection.FormatConditions(2).Borders(xlTop).Weigh t = xlHairline
Selection.FormatConditions(2).Borders(xlBottom).We ight = xlHairline

theo mình thì bạn không nên thay đổi, không đáng phải như vậy.

Hơn nữa không nên lạm dụng FormatConditions vì nó như con dao 2 lưỡi. Vì nó cũng tương tự như công thức.

Chúng ta sử dụng VBA một phần vì muốn File nhẹ đi, tính toán nhanh hơn, tránh để công thức sống (tính toán lại mỗi khi có thay đổi)

Khi đó bạn thiết lập FC cho cả hàng ngàn dòng cũng giống như bạn thiết lập công thức vậy. Vì vậy File sẽ nặng hơn nhiều.

Nên thiết lập 1 vòng lặp để set màu sắc của các ô thì nhẹ hơn

Vấn đề 7 : tạo DEFINAME
Hỏi : Mình có sheet1, sheet2,…. Mình muốn tạo definename gồm các tên sau đây : MangA, MangB, MangC. Muốn mở sheet mới, chạy lệnh thì đều tạo được các definename này, TẤT CẢ CÁC SHEET KHI THỰC HIỆN LỆNH ĐỀU CÓ THỂ TẠO CÁC DEFINENAME TRÙNG TÊN, PHỤ THUỘC VÀO DỮ LIỆU CỘT 5 CỦA CÁC SHEET. Code hiện nay của mình tạo definename ở sheet1, qua sheet2 tạo thì sheet1 mất, phải làm sao?

ActiveWorkbook.Names.Add Name:="MangA", RefersToR1C1:="=OFFSET(R8C23,0,0,COUNTA(R8C5:R1000 C5),1)"
ActiveWorkbook.Names.Add Name:="MangB", RefersToR1C1:="=OFFSET(R8C16,0,0,COUNTA(R8C5:R1000 C5),1)"
ActiveWorkbook.Names.Add Name:="MangC", RefersToR1C1:="=OFFSET(R8C5,0,0,COUNTA(R8C5:R1000C 5),1)"

Bạn phải học cách sử dụng Name cục bộ và Name toàn cục
bạn tham khảo ở đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=2734

Vấn đề 8 : Hỏi về ComboBox
Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1
End Sub
Sửa lại thế nào để ComboBox chỉ thực thi trong cột A của Sheet : NKC thôi ?????

bạn chỉ cần sửa lại
(ComboBox thuộc sheet NKC)

Mã:

Private Sub ComboBox1_Change()
    If ActiveCell.Column = 1 Then ActiveCell.Value = ComboBox1.Value
End Sub

Chúc thành công.

P/S : Mệt quá!!!!!!!!

vanphuc1701 · 15/7/07

Cảm ơn mọi người đã quan tâm. Để mình xem từ từ!!

Mr Okebab · 15/7/07

vanphuc1701 đã viết:
Cảm ơn mọi người đã quan tâm. Để mình xem từ từ!!

Câu trả lời hơi bị dã man !!!

Bạn là người post mà còn nói thế thì người khác sẽ như thế nào ??

Thân!

vanphuc1701 · 16/7/07

Câu trả lời hơi bị dã man !!!

Bạn là người post mà còn nói thế thì người khác sẽ như thế nào ??

Thân!

Thôi mà, Mr Okebad. Mình đã thanh minh trước là mình đang tập tành làm VBA mà. Có một lạot câu hỏi như thế là vì cứ làm tới đâu "bí" thì ghi chú lại để hỏi nên dài thế thôi. Cảm ơn mọi người nhiều nhiều lắm vì đã dành chút thời gian trả lời. Nhưng còn một vấn đề nữa sao không thấy cao thủ nào quan tâm nhỉ : code để xem tính nhanh chậm của VBA để xét các các trường hợp tách gộp nói chung và các vấn đề nahnh chậm khác nói riêng.

Cảm ơn mọi người. Chào đoàn kết.

voda · 17/7/07

Vấn đề 1a :
Giả sử từ E8:E1000 có 75 dòng dữ liệu. Nhưng A5 =1????; B5=75 . Sửa lại thế nào để ko cần “mangdem" vẫn có đáp số đúng.
Sub Vidu()
Dim mangdem As Range
Set mangdem = ActiveSheet.Range("E8:E1000")
Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA(" E8:E1000")
Range("B5").Value = ActiveSheet.Application.WorksheetFunction.CountA(m angdem)
End Sub
Đáp số : A5 =1 không đúng ý mong đợi ; B5=75 đúng ý .
Tại sao A5 =1. Sửa lại thế nào để ko cần thông qua “mangdem" nhưng đáp số B5 vẫn là 75 không ???.

Theo mình có thể sửa lại như thế này để xài đỡ:

Range("A5").Value = "=CountA(E8:E1000)"

Còn vì sao ô A5 luôn bằng 1 thì mình cũng chưa hiểu.
Thân

ThuNghi · 17/7/07

Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA(" E8:E1000"), đúng là " E8:E1000" chỉ có 1 khác với range()
Phải là như sau mới OK. Để ý nếu có công thức thì counta vẫn đếm.

Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA(Range(" E8:E1000"))

Nguyễn Duy Tuân · 17/7/07

Không nên dùng kiểu gọi ActiveSheet.Application. mà nên thay là Application. vì gọi thừa đối tượng ActiveSheet

Range("A5").Value = ActiveSheet.Application.WorksheetFunction.CountA(Range("E8:E1000"))
Đổi thành
Application.WorksheetFunction.CountA(Range("E8:E1000"))

Đối số trong các hàm là một vùng như là An:Bm thì trong VBA phải gọi Range("An:Bm")

Sử dụng công thức có sẵn của Excel trong VB-Gán công thức SUMPRODUCT -Hỏi về CASE ...

vanphuc1701

Thành viên mới

chibi

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

vanphuc1701

Thành viên mới

Mr Okebab

Ngon Ngất Ngây

Mr Okebab

Ngon Ngất Ngây

vanphuc1701

Thành viên mới

Mr Okebab

Ngon Ngất Ngây

vanphuc1701

Thành viên mới

voda

Thành viên tiêu biểu

ThuNghi

Hãy cho rồi sẽ nhận!

Nguyễn Duy Tuân

Nghị Hách

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

Đếm ngược thời gian

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