Vấn đề thêm dòng và copy công thức (1 người xem)

[ninhhai]

Em có một file như file vidu.

Trong file, cứ một hàng có công thức thì lại có một hàng trống bên trên để lấy dữ liệu cho công thức ở hàng dưới.
Em muốn mỗi khi một trong các ô từ cột B tới cột N của hàng thứ 2 từ dưới lên (trong file vidu là B9:N9) được nhập dữ liệu thì excel tự động thêm 2 dòng tương tự xuống dưới , mong các anh giúp đỡ.

Còn một vấn đề khác: trong file em có bỏ vào 1 code để mỗi khi chọn hàng và nhấn nút thì Excel sẽ copy dữ liệu của hàng đó và paste vào một vị trí khác trong bảng tính. Bây giờ em muốn nhấn nút trước rồi mới chọn hàng và chỉ cho phép chọn hàng lẻ đã có dữ liệu có được không ạ?

Nhân tiện các anh cho em hỏi, trong file vidu rất ít dữ liệu, tại sao dung lượng lại lớn như vậy, khi tải lên diễn đàn em mới để ý nhưng tìm chưa ra.

Em xin cảm ơn.

 

[phucbugis]

Em có một file như file vidu.

Trong file, cứ một hàng có công thức thì lại có một hàng trống bên trên để lấy dữ liệu cho công thức ở hàng dưới.
Em muốn mỗi khi một trong các ô từ cột B tới cột N của hàng thứ 2 từ dưới lên (trong file vidu là B9:N9) được nhập dữ liệu thì excel tự động thêm 2 dòng tương tự xuống dưới , mong các anh giúp đỡ.

bạn tải file đính kèm, cho chạy Macro.
Double click vào ô màu cam để Tạo 2 rows mới (code còn thiếu là xoá nội dung của 2 rows cũ)

Còn một vấn đề khác: trong file em có bỏ vào 1 code để mỗi khi chọn hàng và nhấn nút thì Excel sẽ copy dữ liệu của hàng đó và paste vào một vị trí khác trong bảng tính. Bây giờ em muốn nhấn nút trước rồi mới chọn hàng và chỉ cho phép chọn hàng lẻ đã có dữ liệu có được không ạ?

bạn tạo thêm 1 ít số liệu + giải thích lại để mọi người dễ hiểu hơn.

Nhân tiện các anh cho em hỏi, trong file vidu rất ít dữ liệu, tại sao dung lượng lại lớn như vậy, khi tải lên diễn đàn em mới để ý nhưng tìm chưa ra.

mình đã kiểm tra Name + các Object mà ko thấy, mà chỉ thấy 1 tí code của CommandButton1_Click --> mình nghi là file đó dính phải Virus. ---> đã xử lý bằng cách Convert sang đuôi xlsx để xóa sạch Macro, sau đó copy code cũ vào file mới --> dung lượng file giảm đi rất nhiều .
'- - -
bạn check lại công thức từ cột B->N xem có cần thiết phải dùng công thức mảng ko?

'----
file đính kèm ----> #14

 

[ninhhai]

Cám ơn anh đã nhiệt tình giúp đỡ. Nhưng cái em muốn là excel sẽ tự động copy thêm 2 hàng nữa khi em nhập 1 số vào 1 ô bất kỳ trong các ô từ cột B tới cột N của hàng thứ 2 từ dưới lên. (Ví dụ khi em nhập số 1 vào ô E9 )

 

[phucbugis]

Cám ơn anh đã nhiệt tình giúp đỡ. Nhưng cái em muốn là excel sẽ tự động copy thêm 2 hàng nữa khi em nhập 1 số vào 1 ô bất kỳ trong các ô từ cột B tới cột N của hàng thứ 2 từ dưới lên. (Ví dụ khi em nhập số 1 vào ô E9 )

hehe,
vậy thì phải cài thêm khá nhiều cái If, bạn tải tiếp file về xem sao nhé !

'----
file đính kèm ----> #14

 

[ninhhai]

hehe,
vậy thì phải cài thêm khá nhiều cái If, bạn tải tiếp file về xem sao nhé !

Cảm ơn anh rất nhiều, đây đúng là cái em đang cần. Nhưng em chưa hiểu rõ code. Anh có thể giải thích cho em được không? Có cách nào để khi ta xóa dữ liệu ở target thì 2 dòng vừa được copy cũng tự động xóa đi không?

 

[phucbugis]

Cảm ơn anh rất nhiều, đây đúng là cái em đang cần. Nhưng em chưa hiểu rõ code. Anh có thể giải thích cho em được không? Có cách nào để khi ta xóa dữ liệu ở target thì 2 dòng vừa được copy cũng tự động xóa đi không?

- bạn tải tiếp file 1_3 về xem nhé !
- hic, thêm dòng thì điều kiện thoải mái hơn 1 tí, còn xoá thì bạn phải test thật kỹ (vì mình ko trực tiếp thao tác nên có thể cài sót điều kiện nào đó)
- nếu kết quả đúng thì mình sẽ giải thích code sau.

'----
file đính kèm ----> #14

 

[ninhhai]

- bạn tải tiếp file 1_3 về xem nhé !
- hic, thêm dòng thì điều kiện thoải mái hơn 1 tí, còn xoá thì bạn phải test thật kỹ (vì mình ko trực tiếp thao tác nên có thể cài sót điều kiện nào đó)
- nếu kết quả đúng thì mình sẽ giải thích code sau.

Em test rồi, code chạy rất tốt. Mình có thể phát triển thêm nữa là sau khi đã thêm nhiều dòng, ta xóa dữ liệu ở lần nhập đầu tiên hoặc lần nhập bất kỳ thì tất cả những dòng được tạo ra bên dưới sẽ bị xóa không anh? ( ví dụ ta xóa dữ liệu ở hàng 9 thì tất cả các hàng từ 11 trở xuống dưới sẽ bị xóa)

Thêm nữa, với sự kiện Worksheet_change, có cách nào để mới chỉ nhập số ( chưa Enter hoặc rời sang ô khác) thì code đã chạy rồi không? ( vì khi em muốn nhập dữ liệu vào 2 hay nhiều ô trên cùng 1 hàng, khi em bấm trỏ chuột sang ô khác thì code chạy, thêm dòng và select lại ô cũ gây ra cảm giác chạy không mượt.) (không khả thi thì anh đừng quan tâm)

Em cảm ơn.

 

[ninhhai]

Em hỏi ngoài lề một chút.
Em muốn dùng code này để fill 1 ô sang 10 ô bên cạnh. Nhưng không chạy.

Cells(Target.Row, 5).Select
Selection.AutoFill Destination:=Range("e" & Target.Row).Resize(, 10), Type:=xlFillDefault

Em phải viết thế nào mới đúng?

 

[phucbugis]

Em test rồi, code chạy rất tốt. Mình có thể phát triển thêm nữa là sau khi đã thêm nhiều dòng, ta xóa dữ liệu ở lần nhập đầu tiên hoặc lần nhập bất kỳ thì tất cả những dòng được tạo ra bên dưới sẽ bị xóa không anh? ( ví dụ ta xóa dữ liệu ở hàng 9 thì tất cả các hàng từ 11 trở xuống dưới sẽ bị xóa)

Khi dùng code xóa nhiều row cùng lúc mà ko cài kỹ các điều kiện, nếu sơ suất thì ko thể Undo được --> dùng tay xóa chậm hơn 1 tí mà chắc cú (vì sheet đó có số liệu và công thức xen kẽ lần nhau -> nhìn qua loa sẽ khó thấy được)

Thêm nữa, với sự kiện Worksheet_change, có cách nào để mới chỉ nhập số ( chưa Enter hoặc rời sang ô khác) thì code đã chạy rồi không? ( vì khi em muốn nhập dữ liệu vào 2 hay nhiều ô trên cùng 1 hàng, khi em bấm trỏ chuột sang ô khác thì code chạy, thêm dòng và select lại ô cũ gây ra cảm giác chạy không mượt.) (không khả thi thì anh đừng quan tâm)
Em cảm ơn.

Nếu nhập liệu trên UserForm thì việc này rất dễ, nhưng tại sheet thì hiện tại Excel chưa có sự kiện bắt được "Key_Press" (muốn vậy phải tự chế khá nhiều code + khá "chua" )

'- - -
tại #8:
có phải bạn đang hỏi code cho nút CommandButton1_Click? (nếu đúng thì mình đã có hỏi bạn ở #2 mà chưa thấy bạn trả lời ?)

 

[ninhhai]

không, em dang viết 1 code khác, đó chỉ là 1 đoạn trong code, nếu cụ thể như:

Range("E18").Select
Selection.AutoFill Destination:=Range("E18:N18"), Type:=xlFillDefault

thì chạy.

Nhưng viết:

Cells(Target.Row, 5).Select
Selection.AutoFill Destination:=Range("e" & Target.Row).Resize(, 10), Type:=xlFillDefault

thì lại không được.

 

[ninhhai]

Khi dùng code xóa nhiều row cùng lúc mà ko cài kỹ các điều kiện, nếu sơ suất thì ko thể Undo được --> dùng tay xóa chậm hơn 1 tí mà chắc cú (vì sheet đó có số liệu và công thức xen kẽ lần nhau -> nhìn qua loa sẽ khó thấy được)

Code xóa dữ liệu còn một vấn đề nữa. Nếu target.row có nhiều ô chứa dữ liệu, khi cùng lúc xóa tất cả các ô thì code không chạy. code chỉ chạy khi lần lượt xóa từng ô trong row tới khi không còn ô nào có dữ liệu. Khắc phục vấn đề này có khả thi không anh?

 

[phucbugis]

Code xóa dữ liệu còn một vấn đề nữa. Nếu target.row có nhiều ô chứa dữ liệu, khi cùng lúc xóa tất cả các ô thì code không chạy. code chỉ chạy khi lần lượt xóa từng ô trong row tới khi không còn ô nào có dữ liệu. Khắc phục vấn đề này có khả thi không anh?

- trong sự kiện Worksheet_Change bạn thử off dòng này : If Target.Count = 1 Then thì sẽ bị báo lỗi ở dòng này If Target <> "" Then --> 2 "ông" này sẽ bị chõi với nhau
=> muốn khắc phục thì thêm On Error Resume Next (OERN) bên trên If Target <> "" Then, việc cài OERN giống như "con dao 2 lưỡi" bạn ko nên dùng khi chưa hiểu rõ cách dùng (trừ trường hợp đặc biệt nào đó)

- Code đó mình đặt khá nhiều bẫy lỗi (If, If ...) rồi và đã tránh cài nó vào, mục đích để vậy là nhằm biết chính xác được còn xảy ra lỗi ở vị trí nào nữa ko?

 

[ninhhai]

Code xóa không bị lỗi. Nó vẫn chạy tốt khi Target="". Tuy vậy nó sử dụng sự kiện Worksheet_change, target chỉ là một cell nên chỉ khi xóa cell cuối cùng trong row code mới chạy. Khi xóa tất cả các cell cùng một lúc thì không chạy.

 

[phucbugis]

không, em dang viết 1 code khác, đó chỉ là 1 đoạn trong code, nếu cụ thể như:
Range("E18").Select
Selection.AutoFill Destination:=Range("E18:N18"), Type:=xlFillDefault
thì chạy.

Nhưng viết:
Cells(Target.Row, 5).Select
Selection.AutoFill Destination:=Range("e" & Target.Row).Resize(, 10), Type:=xlFillDefault
thì lại không được.

- câu hỏi này mình cũng hơi lớ ngớ khi mới tiếp xúc với VBA , lúc đó mình cũng muốn cài đặt ông Target vào module để dùng cho tiện nhưng ko được.
- mình nghĩ tại vì "hàm, công thức, phương thức, hichic" Target. chỉ dùng riêng cho sự kiện tại Worksheet hoặc ThisWorkbook.

'- - - -
để sửa lại code trên có thể viết lại thành Sub test2

Sub test1() 'OK
    Range("E18").Select
    Selection.AutoFill Destination:=Range("E18:N18"), Type:=xlFillDefault
End Sub

Sub test2()
    'Cells(Target.Row, 5).Select 'error
    'Selection.AutoFill Destination:=Range("e" & Target.Row).Resize(, 10), Type:=xlFillDefault 'error
    Cells(18, 5).Select
    Selection.AutoFill Destination:=Range("e" & ActiveCell.Row).Resize(, 10), Type:=xlFillDefault
End Sub

'- - -
nếu bạn muốn khai báo Target trung gian qua Module thì có thể làm theo cách sau:
Tại Module2

Public rTarget As Range

Sub test3(rTarget As Range)
    rTarget.Select
    Selection.AutoFill Destination:=Range("e" & rTarget.Row).Resize(, 10), Type:=xlFillDefault
End Sub

Tại sự kiện Worksheet (Sheet1) (mình cài thử với Double_Click)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set rTarget = Target

    If Target.Address = "$E$18" Then
        Cancel = True
        Call test3(rTarget)
    End If
    
    'neu dung` code cho Cot E tu` row 10 tro? xuong'
    'If Target.Column = 5 And Target.Row >= 10 Then
        'Cancel = True
        'Call test3(rTarget)
    'End If
    
End Sub

Link: https://www.mediafire.com/?27lrm5h88kt1zs6

 

[ninhhai]

Cuối cùng em chọn copy->paste, tuy dài dòng hơn 1 chút nhưng mà dễ.

 

[ninhhai]

- trong sự kiện Worksheet_Change bạn thử off dòng này : If Target.Count = 1 Then thì sẽ bị báo lỗi ở dòng này If Target <> "" Then --> 2 "ông" này sẽ bị chõi với nhau
=> muốn khắc phục thì thêm On Error Resume Next (OERN) bên trên If Target <> "" Then, việc cài OERN giống như "con dao 2 lưỡi" bạn ko nên dùng khi chưa hiểu rõ cách dùng (trừ trường hợp đặc biệt nào đó)

- Code đó mình đặt khá nhiều bẫy lỗi (If, If ...) rồi và đã tránh cài nó vào, mục đích để vậy là nhằm biết chính xác được còn xảy ra lỗi ở vị trí nào nữa ko?

Em đã hoàn chỉnh được code của anh phucbuis rồi. Bây giờ kể cả copy paste nhiều ô vào target range hay xóa nguyên dòng target range, code vẫn chạy.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vung As Range, vung1 As Range, vung2 As Range
Dim wsFunc As WorksheetFunction
If Not Intersect(Target, Range("e:N")) Is Nothing Then
If Target.Count >= 1 Then
If Target.Row >= 16 Then
If Target.HasFormula = False Then
Set wsFunc = Application.WorksheetFunction
Set vung1 = Range("e" & Target.Row).Resize(, 10)
Set vung2 = Range("e" & Target.Offset(2, 0).Row).Resize(, 10)
'them 2 row
If wsFunc.CountA(vung1) <> 0 Then
If Target.Offset(1).HasFormula = True And Target.Offset(3).HasFormula = False Then
Application.ScreenUpdating = False
Rows(Target.Row).Resize(2).Copy
Cells(Target.Row + 2, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells(Target.Row + 2, 5).Resize(, 10).ClearContents
Target.Select
Application.ScreenUpdating = True
End If
End If
'xoa' 2 row
If wsFunc.CountA(vung1) = 0 Then
If Target.Offset(3).HasFormula = True And Target.Offset(5).HasFormula = False Then
If wsFunc.CountA(vung1) + wsFunc.CountA(vung2) = 0 Then
Rows(Target.Offset(2, 0).Row).Resize(2).EntireRow.Delete
End If
Set vung1 = Nothing: Set vung2 = Nothing
End If
End If
End If
End If
End If
End If
End Sub

Cảm ơn anh rất nhiều.