Thay công thức bằng VBA

[le tin]

Tôi muốn thay những công thức bằng code nhưng mù tịt vụ này , và các bạn cho biết nếu số liệu đến 5000 dòng thì nó có chậm hơn dùng công thức ?
Cám ơn

 

[SA_DQ]

Trước hết ta thực hiện việc tự động nhậpvô 'B'

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns("A:A")) Is nohing Then _
     Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],nhap!C[-1]:C,2,0)"
End Sub

Bạn chép cái con macro này vô CS VBE của sheetName có tên là 'Xuat'
Nhập vài trị mã hàng vô ô trống cột 'A'

Còn hai cột khác bạn thử sức trước đi!

 

[anhphuong]

Sửa một tý tẹo thôi

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Columns("A:A")) Is nohing Then_
     Target.Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],nhap!C[-1]:C,2,0)"
End Sub

Bạn chép cái con macro này vô CS VBE của sheetName có tên là 'Xuat'
Nhập vài trị mã hàng vô ô trống cột 'A'

Còn hai cột khác bạn thử sức trước đi!

Sửa lại một tý: Is Nothing Then

@ HYen17 : Bác dạo này nhiều việc quá nên ...nhầm

 

[Po_Pikachu]

Gửi bạn file đã xử lý theo yêu cầu! Thân.

 

[le tin]

Còn phần SL vượt quá , nó ko báo,và qui tròn cột DONGIA

 

[Po_Pikachu]

Vậy nó va chạm với DK "C9 = 4 Dư 2 và không cho nhập" thì bạn giải quyết làm sao? Mà sao DK 2/ của bạn chỉ áp dụng cho C9 thôi vậy, và không biết có giới hạn số Dư đó không. Mình không hiểu tình trạng đó có liên quan gì đến số liệu gốc không? Bạn nên nói rõ hơn quá trình thay đổi số liệu của bạn khi có khách hàng đặt hàng,v.v... Thân.

 

[cadafi]

Vậy nó va chạm với DK "C9 = 4 Dư 2 và không cho nhập" thì bạn giải quyết làm sao?
Mà sao DK 2/ của bạn chỉ áp dụng cho C9 thôi vậy, và không biết có giới hạn số Dư đó không. Mình không hiểu tình trạng đó có liên quan gì đến số liệu gốc không?
Bạn nên nói rõ hơn quá trình thay đổi số liệu của bạn khi có khách hàng đặt hàng,v.v...
Thân.

Vấn đề này đưa ra là để kiểm soát tồn kho đấy bạn Po_Pikachu. Nếu số lượng xuất kho vượt quá số tồn hiện tại thì không cho xuất! Số lượng tồn tính bằng cách lấy tổng số nhập của mặt hàng đó bên sheet("Nhap") trừ đi tổng số lượng hàng xuất của mặt hàng đó (kể cả số lượng vừa mới gõ vào), nếu hiệu số này < 0 thì không cho xuất! Sau khi gõ số lượng xong, excel sẽ thông báo số tồn kho của mặt hàng đó luôn. Đại khái là sau khi update cột số lượng, thì sẽ có một msgbox hiện ra và thông báo là "số dư/tồn còn lại của mã hàng xyz là ....!"

Mình xin phép gửi đoạn code này, ngắn hơn của Po_Pikachu nhưng giải quyết gọn vấn đề này, bạn có thể xem thêm file đính kèm để test thử nhé:

Private Sub Worksheet_Change(ByVal Target As Range)
''//ca_dafi: Code này chép vào sheet("xuat")
On Error Resume Next
''//Khi nhập Mã hàng sẽ lấy tên hàng:
If Not Intersect(Target, [A4:A65000]) Is Nothing Then
Target.Offset(, 1) = Application.WorksheetFunction.VLookup(Target, Sheets("nhap").[A:D], 2, 0)
End If
''//=====================================================================================================
''//Kiểm tra cột số lượng có phải là kiểu số không? Nếu có mới làm tiếp.
''//Sau đó kiểm tra số lượng tồn trước khi xuất, nếu số âm (<0) thì không cho xuất:
 
If Not Intersect(Target, [C4:C65000]) Is Nothing Then
    If IsNumeric(Target) = False Or Target.Offset(, -2).Value = "" Then
        MsgBox ("Phải là kiểu dữ liệu số, Hoặc phải nhập Mã hàng trước")
        Exit Sub
    Else
        Dim TongSLNhap, TongGTNhap, TongSLXuat As Double
        With Application.WorksheetFunction
            TongGTNhap = .SumIf(Sheets("nhap").[A:A], Target.Offset(, -2), Sheets("nhap").[D:D])
            TongSLNhap = .SumIf(Sheets("nhap").[A:A], Target.Offset(, -2), Sheets("nhap").[C:C])
            TongSLXuat = .SumIf(Sheets("xuat").Range("A4:A" & Target.Row), _
                                          Target.Offset(, -2), Sheets("xuat").Range("C4:C" & Target.Row))
        End With
        If TongSLNhap - TongSLXuat < 0 Or TongSLNhap = 0 Then
            MsgBox ("Xuất vượt quá số tồn!")
            Exit Sub
        Else
            MsgBox ("Số tồn còn lại của Mã Hàng: " & Target.Offset(, -2) & " là: " & (TongSLNhap - TongSLXuat))
            Target.Offset(, 1).Value = Round(TongGTNhap / TongSLNhap,0)              ''Đơn giá xuất, quy tròn
            Target.Offset(, 2).Value = Target.Value * Target.Offset(, 1).Value           ''Số tiền xuất
        End If
    End If
End If
End Sub

 

[le tin]

Cảm ơn bạn,còn qui tròn DONGIA thì sao

 

[cadafi]

...........còn qui tròn DONGIA thì sao

Thì bạn dùng hàm Round(). Bạn xem lại code bài dưới, mình đã sửa rồi đấy!

 

[Po_Pikachu]

@Ca_Dafi: Cảm ơn bác nhiều! Vì em không hoạt động trong lĩnh vực kế toán nhiều nên không rành viềc xuất-nhập nhiều! Cảm ơn bác nha! @Le tin: Gửi file lại cho bạn nè! Thân.

 

[le tin]

To Ca_dafi Khi nhập mã hàng để lấy tên ,nếu không có thì thông báo "Không có" thì thêm như thế nào (đọc tiếng Việt được) .
Còn nữa Khi quét 1 vùng để xóa thì bị lỗi phải thoát chương trình như file kèm theo,
Cảm ơn

 

[Po_Pikachu]

Hai yêu cầu sau đã hoàn thành rồi, còn yêu cầu về Tiếng Việt thì chịu thôi! Msgbox trong VBA không hổ trợ tiếng Việt bạn ơi! Bạn xem file lần nữa nha! Rồi còn vướng gì nữa không? Chỉ cần nhập giá trị ở cột A và C thôi! Thân.

 

[anhphuong]

Để MsgBox hiện tiếng Việt, các bạn sử dụng hàm MsgBoxUni của anh Tuân trên diễn đàn này. Nếu không có liên hệ với tôi.
Thân

P/S:Gửi luôn cho bạn file kèm theo(Mượn tạm File của Ca_dafi để làm)
1/ MsgBox bằng tiếng Việt(Theo anh Tuân)
2/ Chỉnh sửa thêm khi nhập mã ở cột A mà không có thì cho thông báo và thêm cột Tồn Sau khi có thông báo SL tồn qua Msg thì ghi luôn kết quả vào cột Tồn(nếu không thích thì bỏ đi)