Cách lập công thức + Hàm trong VBA thay vì hàm Excel?

[thanhphuongvip]

ANh em cho mình hỏi, thay vì mình dùng hàm trực tiếp trong Excel, nhưng như thế lỡ xóa nhầm công thức thì phải kéo công thức lại, hoặc phải lập hàm lại, điều đó gây bất tiện, giờ mình muốn dùng hàm trực tiếp trong VBA để tính toán, gõ dữ liệu tới đâu sẽ tự tính tới đó (mà ko cần fill trước công thức như trong Excel). Ví dụ tính thành tiền = Đơn giá * số lượng như ở bảng dưới:
(Đối với các hàm khác cũng như vậy)



Xin cảm ơn!

 

[leonguyenz]

ANh em cho mình hỏi, thay vì mình dùng hàm trực tiếp trong Excel, nhưng như thế lỡ xóa nhầm công thức thì phải kéo công thức lại, hoặc phải lập hàm lại, điều đó gây bất tiện, giờ mình muốn dùng hàm trực tiếp trong VBA để tính toán, gõ dữ liệu tới đâu sẽ tự tính tới đó (mà ko cần fill trước công thức như trong Excel). Ví dụ tính thành tiền = Đơn giá * số lượng như ở bảng dưới:
(Đối với các hàm khác cũng như vậy)

View attachment 216544

Xin cảm ơn!

Tham khảo code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E10:E38")) Is Nothing Then
    If Target.Count = 1 And IsNumeric(Target.Value) Then
        Target.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
    End If
End If
End Sub

 

[snow25]

ANh em cho mình hỏi, thay vì mình dùng hàm trực tiếp trong Excel, nhưng như thế lỡ xóa nhầm công thức thì phải kéo công thức lại, hoặc phải lập hàm lại, điều đó gây bất tiện, giờ mình muốn dùng hàm trực tiếp trong VBA để tính toán, gõ dữ liệu tới đâu sẽ tự tính tới đó (mà ko cần fill trước công thức như trong Excel). Ví dụ tính thành tiền = Đơn giá * số lượng như ở bảng dưới:
(Đối với các hàm khác cũng như vậy)

View attachment 216544

Xin cảm ơn!

Bạn thử xem.

 

[be_09]

ANh em cho mình hỏi, thay vì mình dùng hàm trực tiếp trong Excel, nhưng như thế lỡ xóa nhầm công thức thì phải kéo công thức lại, hoặc phải lập hàm lại, điều đó gây bất tiện, giờ mình muốn dùng hàm trực tiếp trong VBA để tính toán, gõ dữ liệu tới đâu sẽ tự tính tới đó (mà ko cần fill trước công thức như trong Excel). Ví dụ tính thành tiền = Đơn giá * số lượng như ở bảng dưới:
(Đối với các hàm khác cũng như vậy)

View attachment 216544

Xin cảm ơn!

Thử code sau:

Sub Nhan_Gia()
    Dim DongCuoi As Long
    Dim i As Long
    DongCuoi = Sheet1.Range("D" & Rows.Count).End(xlUp).Row
    For i = 10 To DongCuoi
        Cells(i, 6) = Cells(i, 4) * Cells(i, 5)
    Next i
End Sub

 

[thanhphuongvip]

Tham khảo code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E10:E38")) Is Nothing Then
    If Target.Count = 1 And IsNumeric(Target.Value) Then
        Target.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
    End If
End If
End Sub

leonguyenz cho mình hỏi chỗ RC... như phần tô đậm nghĩa là gì ạ, và mình có thể viết công thức bằng cách khác ko, vì mình chưa tiếp xúc với công thức loại này nên hơi khó hiểu tí
Target.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"

Cảm ơn bạn!

 

[leonguyenz]

leonguyenz cho mình hỏi chỗ RC... như phần tô đậm nghĩa là gì ạ, và mình có thể viết công thức bằng cách khác ko, vì mình chưa tiếp xúc với công thức loại này nên hơi khó hiểu tí
Target.Offset(, 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
Cảm ơn bạn!

R tương ứng với dòng (Row)
C tương ứng với cột (Column)
C[-2] tương ứng với cột.Offset(,-2) => lấy vị trí trước ô chứa công thức 2 cột (cột đơn giá trong file)
C[-1] tương ứng với cột.Offset(,-1) => lấy vị trí trước ô chứa công thức 1 cột (cột số lượng trong file)
Bạn ghi marco 1 công thức tham chiếu tới ô nào đó sẽ hiểu.

 

[thanhphuongvip]

R tương ứng với dòng (Row)
C tương ứng với cột (Column)
C[-2] tương ứng với cột.Offset(,-2) => lấy vị trí trước ô chứa công thức 2 cột (cột đơn giá trong file)
C[-1] tương ứng với cột.Offset(,-1) => lấy vị trí trước ô chứa công thức 1 cột (cột số lượng trong file)
Bạn ghi marco 1 công thức tham chiếu tới ô nào đó sẽ hiểu.

ah vậy dùng RC nó thay thế cho Offset đúng ko bạn? Mình hiểu rồi!
Giờ đặt ví dụ với một công thức khó hơn như SUMIF, thay vì đặt hàm, bạn giúp mình một code VBA nữa, sau đó mình sẽ rút ra quy luật
mình lấy ví dụ như phần tính tổng thành tiền từng món hàng ở cột bên phải áp dụng sumif sang bảng Hóa đơn (ví dụ vậy)
(Công thức sẽ fill tự động dựa vào dữ liệu ở cột J)

 

[leonguyenz]

ah vậy dùng RC nó thay thế cho Offset đúng ko bạn? Mình hiểu rồi!
Giờ đặt ví dụ với một công thức khó hơn như SUMIF, thay vì đặt hàm, bạn giúp mình một code VBA nữa, sau đó mình sẽ rút ra quy luật
mình lấy ví dụ như phần tính tổng thành tiền từng món hàng ở cột bên phải áp dụng sumif sang bảng Hóa đơn (ví dụ vậy)
(Công thức sẽ fill tự động dựa vào dữ liệu ở cột J)


View attachment 216557

Bạn thử record Marco chưa nhỉ? Nếu bạn thử thì chắc là hiểu được.
Công thức: .FormulaR1C1 = "=SUMIF(R10C2:R14C2,RC10,R10C6:R14C6)"
Dùng thêm 1 lệnh FillDown, nhưng bạn cần phải biết được FillDown tới đâu (lastrow).

 

[thanhphuongvip]

Bạn thử record Marco chưa nhỉ? Nếu bạn thử thì chắc là hiểu được.
Công thức: .FormulaR1C1 = "=SUMIF(R10C2:R14C2,RC10,R10C6:R14C6)"
Dùng thêm 1 lệnh FillDown, nhưng bạn cần phải biết được FillDown tới đâu (lastrow).

Cảm ơn bạn, để mình thử!