mình có file rất nhiều dữ liệu có lúc lên tới cả trăm ngàn dòng nên mỗi lần lập 1 công thức và phải dán value nó lại để cho file bớt nặng nhưng mỗi lần làm như vậy rất mất thời gian, ví dụ như công thức trong file đính kèm.
các bạn có cách nào dùng vba copy dòng trên xong dán value cho dòng dưới lại mà nhanh hơn không giúp mình với!
Chào các bạn!
mình có file rất nhiều dữ liệu có lúc lên tới cả trăm ngàn dòng nên mỗi lần lập 1 công thức và phải dán value nó lại để cho file bớt nặng nhưng mỗi lần làm như vậy rất mất thời gian, ví dụ như công thức trong file đính kèm.
các bạn có cách nào dùng vba copy dòng trên xong dán value cho dòng dưới lại mà nhanh hơn không giúp mình với!
tks!
Không phải bạn à, vì mình vừa làm cái này chế biên đi lung tung và linh nhiều bảng khác nhau nữa, nói chung công thức chồng chéo nhau nên không dùng pivotTable được mình chỉ minh họa 1 công thưc thôi!.
mình có file rất nhiều dữ liệu có lúc lên tới cả trăm ngàn dòng nên mỗi lần lập 1 công thức và phải dán value nó lại để cho file bớt nặng nhưng mỗi lần làm như vậy rất mất thời gian, ví dụ như công thức trong file đính kèm.
các bạn có cách nào dùng vba copy dòng trên xong dán value cho dòng dưới lại mà nhanh hơn không giúp mình với!
chào bạn,
"copy dòng trên xong dán value cho dòng dưới" --> trước đó dòng trên có công thức hay ko?
'--------
hay ý của bạn là:
copy dòng trên xuống, nhưng chỉ copy cột Order và ô công thức ? sau đó chuyển công thức dòng trên thành Value (nếu chuyển luôn dòng dưới thành Value thi` làm gì còn công thức để tính toán khi nhập số liệu ô RMQ)
'--------
p/s: mình vẫn chưa hiểu cách giải thích của #5
Mã:
[COLOR=#000000]Cảm ơn bạn đã quan tâm mình. Có nghĩa là khi lập công thức ở cột [D2] sau đó mình muốn chuyển nó xuống dưới và copy về dạng value để mỗi lần mở file lên cho nó đở nặng, nếu cần tính toán lại mình chỉ cần dùng 1 nút lệnh chẳng hạn nó tính toán lại sau đó lại copy value lại luôn! Nhưng mình sợ nếu thêm nhiềm dòng nữa không biết ổn không có cách nào dùng vba tính toán phân đoạn giảm thiểu bộ nhớ cho nó nhanh hơn không.
[/COLOR]
Cảm ơn bạn đã quan tâm mình. Có nghĩa là khi lập công thức ở cột [D2] sau đó mình muốn chuyển nó xuống dưới và copy về dạng value để mỗi lần mở file lên cho nó đở nặng, nếu cần tính toán lại mình chỉ cần dùng 1 nút lệnh chẳng hạn nó tính toán lại sau đó lại copy value lại luôn! Nhưng mình sợ nếu thêm nhiềm dòng nữa không biết ổn không có cách nào dùng vba tính toán phân đoạn giảm thiểu bộ nhớ cho nó nhanh hơn không.
Cảm ơn bạn đã quan tâm mình. Có nghĩa là khi lập công thức ở cột [D2] sau đó mình muốn chuyển nó xuống dưới và copy về dạng value để mỗi lần mở file lên cho nó đở nặng, nếu cần tính toán lại mình chỉ cần dùng 1 nút lệnh chẳng hạn nó tính toán lại sau đó lại copy value lại luôn! Nhưng mình sợ nếu thêm nhiềm dòng nữa không biết ổn không có cách nào dùng vba tính toán phân đoạn giảm thiểu bộ nhớ cho nó nhanh hơn không.
Cái này vẩn chưa đúng ý mình bạn à. mình chỉ cần copy cột (D) với điều kiện nếu cột b=null thì không làm gì cả còn lại thì copy thôi và nếu dữ liệu cột c có sửa thì mình chỉ việc nhấn nút nó tính toán lại hết còn file của bạn nó không tính.
còn các cột còn lại mình download trực tiếp ra nên không cần copy.
Cái này vẩn chưa đúng ý mình bạn à. mình chỉ cần copy cột (D) với điều kiện nếu cột b=null thì không làm gì cả còn lại thì copy thôi và nếu dữ liệu cột c có sửa thì mình chỉ việc nhấn nút nó tính toán lại hết còn file của bạn nó không tính.
còn các cột còn lại mình download trực tiếp ra nên không cần copy.
'----------
theo như bạn mô tả ---> mình hiểu có 2 trường hợp xảy ra:
#1: cập nhật công thức từng Row khi Material, RQM thay đổi
#2: cập nhật nguyên cột công thức mỗi khi "download trực tiếp ..."
'---------- p/s #9
nếu ko có gì phát sinh mình sẽ post tiếp tại bài này
#1: cập nhật công thức từng Row khi Material, RQM thay đổi
[GPECODE=vb]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B5:B65000,C5:C65000]) Is Nothing Then
If Target.Count > 1 Then Exit Sub '<=> code chi? kich hoat tung` Row
'If Target = "" Then ...
'R=Row, C=Column
Range("D" & Target.Row).FormulaR1C1 = "=SUMIF(R5C2:RC[-2],RC[-2],R5C3:RC[-1])"
With Range("D" & Target.Row)
.Value = .Value
End With
End If
End Sub
[/GPECODE] line2: liên quan đến phương thức Intersect --> bạn tham khảo link sau nhé: Bước đầu về phương thức Intersect (SA_DQ)
line3: If Target.Count > 1 Then Exit Sub <=> nếu ko có điều kiện này --> rất dễ gặp lỗi nếu có sự thay đổi của nhiều ô (khi xóa hoặc paste 1 vùng vào)
line6: để lấy công thức theo dạng RC
- bạn tạo công thức đúng bất kỳ cho cột D,
- mở chức năng Record Macro - chọn 1 ô có chứa công thức - đặt chuột vào thanh Function và Enter - Stop Record --> sẽ ra được code
R5C2 --> R5= Row5, C2= ColumnB
RC[-2] --> RC: vị trị ô đang có công thức (Row bất kỳ), [-2] : di chuyển sang trái 2 cột --> cột B
tham khảo thêm: Kiểu tham chiếu R1C1
line7,8,9: chuyển đổi công thức thành value
ban đầu nó có dạng:
If RC_colB <> MyCount Then
MsgBox "Clear Filter/ Unhide Rows truoc khi Update", vbInformation
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData '|| dang dung` Filter
Cells.EntireRow.Hidden = False '|| Hide Row
End If
'---------
Set ArrMaterial = Range(Range("B" & Rows.Count).End(xlUp), Range("B5"))
'|| Offset(, 2) --> cot. Cong thuc' (cot D)
ArrMaterial.Offset(, 2).FormulaR1C1 = "=SUMIF(R5C2:RC[-2],RC[-2],R5C3:RC[-1])" '|| R=Row, C=Column
ArrMaterial.Offset(, 2).Value = ArrMaterial.Offset(, 2).Value
MsgBox "Update thành công toan` bo^. công thuc' tai. cot. D", , "-GPE-"
Set ArrMaterial = Nothing
End Sub[/GPECODE] line 2, 3: khai báo biến các biến riêng cho Sub line 5: đếm tổng số Row trong 1 sheet (Excel 2003: 65536, Excel 2007: 1048576) line 6: đếm các row đang bị ẩn (có thể đang Filter hoặc Hide Row) line 8,..,13: nhả lọc hoặc Unhide all Row trong sheet đó nếu thỏa điều kiện
mục đích của các line này dùng để hỗ trợ việc xác định vùng tại cột Material (theo kiểu xlUp) line 15: xác định vùng chứa số liệu tại cột B (Material)
- Range("B" & Rows.Count).End(xlUp): vị trí cuối cùng có chứa số liệu tại cột B (động, tùy số liệu mà bạn nhập vào)
- Range("B5"): vị trí đầu tiên (cố định) line 16:liên quan đến thuộc tính Offset, bạn tham khảo 2 link sau:
- Cách chọn ô/dải ô bằng quy trình Visual Basic trong Excel - microsoft
- Index - Lập trình với Form, controls và Objects - handung107
Đúng ý mình rồi cảm ơn bạn nhé, nhưng mình gà quá chưa hiểu cách viết của bạn bạn có thể giải thích hộ mình không?
mình muốn thêm nhiều công thức nữa ở những cột kế bên thì làm sao bạn!
'----------
theo như bạn mô tả ---> mình hiểu có 2 trường hợp xảy ra:
#1: cập nhật công thức từng Row khi Material, RQM thay đổi
#2: cập nhật nguyên cột công thức mỗi khi "download trực tiếp ..."
'---------- p/s #9
nếu ko có gì phát sinh mình sẽ post tiếp tại bài này
#1: cập nhật công thức từng Row khi Material, RQM thay đổi
[GPECODE=vb]
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [B5:B65000,C5:C65000]) Is Nothing Then
If Target.Count > 1 Then Exit Sub '<=> code chi? kich hoat tung` Row
'If Target = "" Then ...
'R=Row, C=Column
Range("D" & Target.Row).FormulaR1C1 = "=SUMIF(R5C2:RC[-2],RC[-2],R5C3:RC[-1])"
With Range("D" & Target.Row)
.Value = .Value
End With
End If
End Sub
[/GPECODE] line2: liên quan đến phương thức Intersect --> bạn tham khảo link sau nhé: Bước đầu về phương thức Intersect (SA_DQ)
line3: If Target.Count > 1 Then Exit Sub <=> nếu ko có điều kiện này --> rất dễ gặp lỗi nếu có sự thay đổi của nhiều ô (khi xóa hoặc paste 1 vùng vào)
line6: để lấy công thức theo dạng RC
- bạn tạo công thức đúng bất kỳ cho cột D,
- mở chức năng Record Macro - chọn 1 ô có chứa công thức - đặt chuột vào thanh Function và Enter - Stop Record --> sẽ ra được code View attachment 123301
R5C2 --> R5= Row5, C2= ColumnB
RC[-2] --> RC: vị trị ô đang có công thức (Row bất kỳ), [-2] : di chuyển sang trái 2 cột --> cột B
line7,8,9: chuyển đổi công thức thành value
ban đầu nó có dạng:
If RC_colB <> MyCount Then
MsgBox "Clear Filter/ Unhide Rows truoc khi Update", vbInformation
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData '|| dang dung` Filter
Cells.EntireRow.Hidden = False '|| Hide Row
End If
'---------
Set ArrMaterial = Range(Range("B" & Rows.Count).End(xlUp), Range("B5"))
'|| Offset(, 2) --> cot. Cong thuc' (cot D)
ArrMaterial.Offset(, 2).FormulaR1C1 = "=SUMIF(R5C2:RC[-2],RC[-2],R5C3:RC[-1])" '|| R=Row, C=Column
ArrMaterial.Offset(, 2).Value = ArrMaterial.Offset(, 2).Value
MsgBox "Update thành công toan` bo^. công thuc' tai. cot. D", , "-GPE-"
Set ArrMaterial = Nothing
End Sub[/GPECODE]
line 2, 3: khai báo biến các biến riêng cho Sub
line 5: đếm tổng số Row trong 1 sheet (Excel 2003: 65536, Excel 2007: 1048576)
line 6: đếm các row đang bị ẩn (có thể đang Filter hoặc Hide Row)
line 8,..,13: nhả lọc hoặc Unhide all Row trong sheet đó nếu thỏa điều kiện
mục đích của các line này dùng để hỗ trợ việc xác định vùng tại cột Material (theo kiểu xlUp)
Thật sự cảm ơn bạn nhờ có bạn mà mình hiểu chút ít về VBA rồi, trong file ví ** của mình mình chỉ mới bỏ 1 công thức. nếu mình thêm nhiều công thức ở nhiều cột khác nhau chẳng hạn như công thức sau =INDEX(CP!$C$4:$C$55,MATCH($G7,CP!$A$4:$A$55,0)) thì làm thế nào!?
tks!
Thật sự cảm ơn bạn nhờ có bạn mà mình hiểu chút ít về VBA rồi, trong file ví ** của mình mình chỉ mới bỏ 1 công thức. nếu mình thêm nhiều công thức ở nhiều cột khác nhau chẳng hạn như công thức sau =INDEX(CP!$C$4:$C$55,MATCH($G7,CP!$A$4:$A$55,0)) thì làm thế nào!?
tks!
- cũng làm tương tự cột D vậy đó, bạn xem phần hướng dẫn ở trên và cài thử code xem (nếu có nhiều cột có chứa công thức --> code có thể dài ra)
- nếu ko được nữa, bạn chuẩn bị đầy đủ các cột (đúng với thực tế, ko cần điền nhiều số liệu) rồi mình dựa vào đó và sửa code tiếp cho bạn.
- cũng làm tương tự cột D vậy đó, bạn xem phần hướng dẫn ở trên và cài thử code xem (nếu có nhiều cột có chứa công thức --> code có thể dài ra)
- nếu ko được nữa, bạn chuẩn bị đầy đủ các cột (đúng với thực tế, ko cần điền nhiều số liệu) rồi mình dựa vào đó và sửa code tiếp cho bạn.
đây là file của mình bạn xem 3 cột mình tô màu vàng nhé. trong file cột thứ nhất bạn đã làm vba giúp mình rồi còn 3 cột còn lại mình muốn chuyển vào vba luôn. Nếu có cách nào nhanh hơn dùng 1 cột thì bạn góp ý giúm mình nhé!
đây là file của mình bạn xem 3 cột mình tô màu vàng nhé. trong file cột thứ nhất bạn đã làm vba giúp mình rồi còn 3 cột còn lại mình muốn chuyển vào vba luôn. Nếu có cách nào nhanh hơn dùng 1 cột thì bạn góp ý giúm mình nhé!
- sự kiện WS_change mình đang cài riêng cho cột G, M (bạn có thể thêm hoặc bớt cột tuỳ ý)
- để kiểm tra các công thức tạo ra ---> bạn tạm off các dòng .Value = .Value (công thức cột STT do ko có sẵn --> có thể mình cài sai --> bạn tự điều chỉnh nhé)
- việc cập nhập toàn bộ công thức cho 3 cột, tùy thuộc vào số liệu cột Material1 hiện có.
- sự kiện WS_change mình đang cài riêng cho cột G, M (bạn có thể thêm hoặc bớt cột tuỳ ý)
- để kiểm tra các công thức tạo ra ---> bạn tạm off các dòng .Value = .Value (công thức cột STT do ko có sẵn --> có thể mình cài sai --> bạn tự điều chỉnh nhé)
- việc cập nhập toàn bộ công thức cho 3 cột, tùy thuộc vào số liệu cột Material1 hiện có.
Chào các bác, em có file này nhờ các bác chỉ giáo giúp với ạ. Ở cột E em muốn đánh số thứ tự nếu như cột X có giá trị là "x", mỗi lần cột X thêm giá trị "x" là cột E sẽ thêm số thứ tự ạ. Em cảm ơn các bác
Chào các bác, em có file này nhờ các bác chỉ giáo giúp với ạ. Ở cột E em muốn đánh số thứ tự nếu như cột X có giá trị là "x", mỗi lần cột X thêm giá trị "x" là cột E sẽ thêm số thứ tự ạ. Em cảm ơn các bác
