Nhờ giải thích code: Tự nhảy kết quả mà không cần Copy công thức. (1 người xem)

[tuan_anhbm]

Chào mọi người !
Mình có câu hỏi về Code nhờ giúp đỡ :
Có 3 cột A, B,C
Cột C=A*B
Làm thế nào để khi nhập số liệu vào cột A và B thì C có kết quả ngay mà không cần phải “kéo chuột”.

Dưới đây là giải pháp mình sưu tầm được, kết quả OK mà chưa hiểu được ý nghĩa các câu lệnh đặng còn ứng dụng vào đề tài khác:
Chuột phải vô thanh SheetsName (dưới cùng bên trái màn hình excel)
Chọn dòng cuối của cửa sổ vừa xuất hiện & chép đoạn mã này vô:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B" & (9 + Target.Row))) Is Nothing Then
With Target
If .Offset(0, -1) <> "" Then .Offset(0, 1) = .Offset(0, -1) * .Value
End With
End If
End Sub

Kết quả:
Nếu cột A&B có dữ liệu thì:
Cột C=A*B
(Đ.kiện: ô hiện hành nằm trong cột C)

Cái hay là ko cần copy công thức mà tự nhảy kết quả khi cột A, B có dữ liệu; ko mang theo công thức -> khó xử lý chỉnh sửa và nặng file.
PP trên tương tự như PP tra mã hiệu đơn giá trong dự toán 97 (nếu ai đã sử dụng DT97 sẽ hiểu liền: Sau khi nhập mã hiệu đơn giá: tự động cho kết quả trong các cột "thứ tự"; "tên công việc"; "đơn vị"; ở dạng text như ví dụ trên để người sử dụng có thể dễ dàng sửa đổi nội dung.)
Vậy nhờ các bạn giải thích giùm ý nghĩa các câu lệnh trên để mình ứng dụng vào công việc dự toán của mình. Cảm ơn.
(Các bạn tải file đính kèm có ví dụ minh họa rất rõ).

 

[PhanTuHuong]

Cái này đã giải thích trên diễn đàn rồi, bạn chịu khó tìm kiếm nhé

 

[tuan_anhbm]

Theo như anh Phan Tự Hướng chỉ dẫn, mình đã Seach trên diễn đàn như “mò kim đáy bể” ... cuối cùng cũng thấy câu trả lời (ở đâu đó trên diễn đàn), phải mất cả tiếng đồng hồ “ngâm cứu” mới tạm thông mấy câu lệnh “chết tiệt”. Dốt VBA quá nên phải đành chịu khó vậy chứ biết làm sao.
Cuối cùng thì mình cũng đã áp dụng được vào trong công việc: (Bảng khối lượng dự toán trong file kèm theo): Nhập mã đơn giá -> tự nhảy STT, tên công việc, đơn vị...
Như vậy về ý nghĩa các câu lệnh là tạm ổn, hôm nay, bước tiếp theo mình muốn được các cao thủ giải đáp cho 2 vấn đề:
1- Làm sao để trả kết quả về dạng text:
Trong ví dụ đính kèm, khi mình muốn kết quả ở dạng text thì nó lại ở dạng công thức, nếu dữ liệu ở cùng chung Sheet tương tự như ví dụ trên thì được rồi, đằng này do phải link đến Sheet khác bằng hàm dò tìm (VLOOKUP) nên mình loay hoay mãi mà chưa chuyển nó về text được. Đúng là “nhân/chia thì biết mà cộng/trừ thì chưa” !
Dạng text mà mình muốn nói ở đây là kết quả ngay sau khi nhập MHDG và nhấn Enter chứ ko phải copy rồi Paste Value để khử công thức.
Các bạn chuột phải vào tên Sheet “BKL” > View Code để xem.
Ở đây mình trích đoạn để các bạn có thể nhìn thấy ngay (thêm gì đó hay sửa các câu lệnh tô đậm (?) ... để kết quả trả về dạng text):
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7:B" & (7 + Target.Row))) Is Nothing Then
With Target
'Ghi STT:
.Offset(0, -1) = "=IF(RC[1]="""","""",COUNT(R7C:R[-1]C)+1)"
'Tra ten cong viec & don vi:
.Offset(0, 1) = "=VLOOKUP(RC[-1],DonGia!R5C1:R19C2,2,0)"
.Offset(0, 2) = "=VLOOKUP(RC[-2],DonGia!R5C1:R19C3,3,0)"
'Don gia nhan cong & may:
'..................
'Thanh tien nhan cong & may:
'..................
End With
End If
End Sub
--------------------------------------
và
2- Lấy mã hiệu của công việc được chọn từ bảng DonGia để gán vào bảng khối lượng:
Cụ thể: Bình thường mình phải tìm từng mã hiệu của 1 công việc trong bảng DonGia sau đó copy trở qua bảng KL để dán và ô MHDG. Bây giờ mình muốn đứng trên bảng DonGia để lần lượt chọn các công việc (nào đó) bằng cách đánh dấu bởi 1 ký tự bất kỳ (<>””, VD: a, b, x...) vào ô cùng hàng trong cột F. Sau khi Enter thì nó tự đưa mã hiệu đơn giá, đơn vị... của công việc đó qua bảng khối lượng. Tức không phải chọn rồi trở qua trở lại nhiều lần giữa 2 bảng.
Mình đã thử áp dụng PP trên (sử dụng Private Sub Worksheet_Change... với ô Target là ô từ E7 trở xuống trên bảng DonGia) nhưng mới chỉ đưa qua được 1 công việc, với các công việc tiếp theo thì chưa được.
---------------------------------------
Vậy các bạn trong diễn đàn có ai đó biết thì giúp mình với, mình đang rất cần cái này.

 

[SA_DQ]

Bạn chép macro dưới đây đè lên toàn bộ cái cũ của bạn & thử nghiệm

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B7:B777")) Is Nothing Then
   Dim Sh As Worksheet, Rng As Range, sRng As Range
   
   Set Sh = Worksheets("DonGia")
   Set Rng = Sh.Range(Sh.[A4], Sh.[a65500].End(xlUp))
   Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
   If sRng Is Nothing Then
      Target.Value = "Khong Co Du Lieu Nay!"
   Else
      With Target
         .Offset(, 1).Value = sRng.Offset(, 1).Value
         .Offset(, 2).Value = sRng.Offset(, 2).Value
         .Offset(, 4).Value = sRng.Offset(, 3).Value
         .Offset(, 5).Value = sRng.Offset(, 4).Value
      End With
      Target.Offset(, 3).Select
   End If
End If
End Sub

Do đang vội, nên chưa giải quyết câu sau của bạn; & hãy đợi thêm nha!

 

[tuan_anhbm]

Cảm ơn bạn SA_DQ nhiều nhé,
Đang lúc bí gặp tri ân thật ko gì bằng.
Bạn có phải dân xây dựng ko mà đã rành VBA còn rành vụ này quá vậy?
Sau khi thử nghiệm kết quả = OK, đã cho kết quả text rồi lại còn nhảy qua ô khối lượng để chờ nhập kh.lượng, quá chuyên nghiệp.
-------------------
Tuy vậy có 1 chỗ cần xem xét lại: Nếu Delete 1 mã hiệu nào hoặc đặt chuột vào 1 ô mã hiệu trống nào đó rồi Enter thì xuất hiện trong ô đó: "Khong Co Du Lieu Nay!", đồng thời ko cách gì xóa được ô MHDG đó. Mình sửa thế này:
If sRng Is Nothing Then
Target.Offset(, 1).Value = "Khong Co Du Lieu Nay!"
Else
đúng ko SA_DQ?
-------------------
Hơi khó để mình hiểu được mấy câu lệnh này của SA_DQ, nhưng thấy cho kết quả như mình muốn là OK rồi, phần còn lại mình sẽ tự tìm hiểu thêm.

Lúc nào rãnh tay, SA_DQ lại "phóng" cho vài chiêu để giải quyết nốt phần 2 của mình nhé. Tiện thể cho mình xin thêm câu lệnh ghi STT ngay sau With Target luôn đi
(.Offset(, -1).Value = gì đó ?....Value) để mỗi khi có 1 MHDG thì nó lại tự nhảy 1, 2, 3...) ở dạng text nhé.

 

[huuthang_bd]

Bạn thử file này xem. Gõ chữ "x" vào cột F sheet DonGia ở những công việc được chọn. Gõ lần lượt từng ô nha.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F7:F65536")) Is Nothing And Target.Value = "x" Then
   With Sheets("BKL").[A65536].End(xlUp).Offset(1)
   .Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
   .Offset(, 1).Value = Target.Offset(, -5).Value
   .Offset(, 2).Value = Target.Offset(, -4).Value
   .Offset(, 3).Value = Target.Offset(, -3).Value
   .Offset(, 5).Value = Target.Offset(, -2).Value
   .Offset(, 6).Value = Target.Offset(, -1).Value
   End With
End If
End Sub

Bạn sửa lại một chút.
Dòng:

If Not Intersect(Target, Range("F7:F65536")) Is Nothing And Target.Value = "x" Then

Sửa lại thành:

If Not Intersect(Target, Range("F5:F65536")) Is Nothing And Target.Value = "x" Then

 

[tuan_anhbm]

Mình chưa thử kỹ nhưng trước mắt như vậy là quá tuyệt vời rồi Thắng ơi!
Mình sẽ test kỹ hơn và xem xét tới các tình huống đặc biệt, có gì lại cho mình hỏi thêm nhé.
Cảm ơn bạn nhiều nhiều...

 

[SA_DQ]

Mình xin đề xuất quy trình mới: Gộp 2 trong 1

Bạn đến trang 'DonGia', hãy nhập trị số dòng nào muốn chép vô ô của cột 'F' của dòng ấy
(Ví dụ muốn chép dòng thứ 5, ta nhập số 5 vô [F5])
Macro sau sẽ làm tất các việc còn lại cho bạn - Nếu rảnh quá, mời nhau uống cafe nha!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim Sh As Worksheet, Rng As Range

Set Sh = Worksheets("BKL"):         Set Rng = Sh.[B65500].End(xlUp)
If Not Intersect(Target, Range("F5:F" & [a65500].End(xlUp).Row)) Is Nothing _
   And Target.Value = Target.Row Then
   With Rng
      .Offset(1).Resize(, 3).Value = Target.Offset(, -5).Resize(, 3).Value
      .Offset(1, 4).Resize(, 2).Value = Target.Offset(, -2).Resize(, 2).Value
      With .Offset(, -1)
         .Offset(1).Value = IIf(.Value = "", 1, .Value + 1)
      End With
   End With
End If
Sh.Select:                          Rng.Offset(1, 3).Select
End Sub

(Chú í: macro này phải cho vô trang tính 'DonGia' mới được à nha!)

 

[huuthang_bd]

Nếu kết hợp thì kết hợp như thế này sẽ hay hơn:

Private Sub Worksheet_Deactivate()
If Application.WorksheetFunction.Count([F:F]) = 0 Then Exit Sub
For Each Rng In [F:F].SpecialCells(xlCellTypeConstants, 1)
   With Sheets("BKL").[A65536].End(xlUp).Offset(1)
   .Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
   .Offset(, 1).Resize(, 3).Value = Rng.Offset(, -5).Resize(, 3).Value
   .Offset(, 4).Value = Rng.Value
   .Offset(, 5).Resize(, 2).Value = Rng.Offset(, -2).Resize(, 2).Value
   End With
Next
[F:F].ClearContents
End Sub

Dán code vào sheet DonGia. Nhập khối lượng vào cột F sheet DonGia.

 

[tuan_anhbm]

Chào HYen17 và Hữu Thắng.
Mình đã thử qui trình mới (2 trong 1) của cả 2 bạn.
Nhận xét ban đầu:
Qui trình này của HYen cũng hay nhưng nó đòi hỏi mình trước khi nhập số liệu vào cột "F" (= số dòng của công việc muốn chọn) phải ngó qua bên trái để đối chiếu coi công việc đó thuộc dòng thứ mấy ? mình nghĩ gõ 1 ký tự vẫn nhanh hơn chứ ?
------------------------
Ngoài ra: Qui trình của HYen lại ko thể nhập được khi có từ 2 công việc giống nhau. (bạn kiểm tra lại giúp mình nhé: Nếu có từ 2 công việc giống nhau (trùng MHDG) bên bảng kh.lượng thì phải làm sao ?)
------------------------
Sub của HuuThang_bd mình thử nghiệm thấy nhập được (Cả Sub trước và Sub sau này). Cứ đặt chuột vào dòng công việc nào lập lại đó (thuộc cột F bảng DonGia) rồi nhấn Enter, trở qua bảng KL là thấy nó nằm chình ình bên đó rồi.
------------------------
Cảm ơn các bạn đã quan tâm giúp mình, khi nào rảnh uống cafe nha!

 

[PhanTuHuong]

Code của 2 bác là dùng cho Excel 2003 về trước, còn 2007 thì số dòng sẽ thay đổi.

 

[tuan_anhbm]

Nhờ sửa Code

Hữu Thắng Bình Định đâu rồi ?
Lại có chút chuyện phải nhờ bạn đây:
Trong Sub của bạn:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F5:F65536")) Is Nothing And Target.Value = "x" Then
With Sheets("BKL").[A65536].End(xlUp).Offset(1)
.Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
.Offset(, 1).Value = Target.Offset(, -5).Value

End With
End If
End Sub
Hiện tại : khi cột “nội dung công việc” (cột C) trong bảng KL có 1 hoặc nhiều dòng diễn giải thì nó đè lên luôn các dòng đó !
Mình muốn Thắng chỉnh lại sao cho: khi cột “nội dung công việc” trong bảng KL có dòng diễn giải thì nó tự bỏ qua các dòng đó để điền kết quả vào dòng kế tiếp. Tức là lấy theo ô dữ liệu cuối của cột C thay vì A. (như trong file đính kèm).
-------------------
Mình thử chỉnh như sau:
With Sheets("BKL").[C65536].End(xlUp).Offset(1)
.Offset(, -2).Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
......
sao bị báo lỗi tại dòng: .Offset(, -2).Value ... ?
--------------------
Giúp mình nhé !
Cảm ơn bạn nhiều.

 

[huuthang_bd]

Hữu Thắng Bình Định đâu rồi ?
Lại có chút chuyện phải nhờ bạn đây:
Trong Sub của bạn:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F5:F65536")) Is Nothing And Target.Value = "x" Then
With Sheets("BKL").[A65536].End(xlUp).Offset(1)
.Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
.Offset(, 1).Value = Target.Offset(, -5).Value

End With
End If
End Sub
Hiện tại : khi cột “nội dung công việc” (cột C) trong bảng KL có 1 hoặc nhiều dòng diễn giải thì nó đè lên luôn các dòng đó !
Mình muốn Thắng chỉnh lại sao cho: khi cột “nội dung công việc” trong bảng KL có dòng diễn giải thì nó tự bỏ qua các dòng đó để điền kết quả vào dòng kế tiếp. Tức là lấy theo ô dữ liệu cuối của cột A thay vì C. (như trong file đính kèm).
-------------------
Mình thử chỉnh như sau:
With Sheets("BKL").[C65536].End(xlUp).Offset(1)
.Offset(, -2).Value = IIf(IsNumeric(.Offset(-1)), .Offset(-1).Value + 1, 1)
......
sao bị báo lỗi tại dòng: .Offset(, -2).Value ... ?
--------------------
Giúp mình nhé !
Cảm ơn bạn nhiều.

Sửa lại như thế này:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F5:F65536")) Is Nothing And Target.Value = "x" Then
   With Sheets("BKL").[C65536].End(xlUp).Offset(1, -2)
   .Value = Application.WorksheetFunction.Max(Sheets("BKL").Range(Sheets("BKL").[A6], .Offset(-1))) + 1
   .Offset(, 1).Value = Target.Offset(, -5).Value
   .Offset(, 2).Value = Target.Offset(, -4).Value
   .Offset(, 3).Value = Target.Offset(, -3).Value
   .Offset(, 5).Value = Target.Offset(, -2).Value
   .Offset(, 6).Value = Target.Offset(, -1).Value
   End With
End If
End Sub

Chú ý: Xóa hết dữ liệu các dòng bên dưới ở cột C.