Price Calculation

[Agnarr]

Hello Everyone!
A code that works perfectly brings from a sheet called "codes" the name and price of a product in my table, specifically in column d.
My problem is that I want to insert in column E the amount taken from each product and have the new price show in column E.
e.g.
one product has a price of 2$ but the cell in column E signifying the quantity is empty (that means 1), at which case I need the price to be shown in column C
another product has a price of 3$ but the cell in column E signifying the quantity has a value of 2, at which case I need the price of 3*2=6 to be shown in column C.

So in column C I have " =IF(E2="";D2;D2*E2) " but I need it integrated inside the vba code.
Please help?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Sh.Range("g:g")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("codes").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(0, -5).Value = fnd.Offset(0, 1).Value
        Target.Offset(0, -3).Value = fnd.Offset(0, 2).Value
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet
    Set ws = Sh
    Call Workbook_SheetChange(ws, ws.Cells(1, 1)) ' Trigger the code for the new sheet
End Sub

 

[aaewalsh]

I understand your requirement. You want to integrate the calculation logic directly into your VBA code instead of relying on a formula in Excel.

Here's a modification to your VBA code to achieve this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Sh.Range("g:g")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("codes").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        ' Copying name and original price
        Target.Offset(0, -5).Value = fnd.Offset(0, 1).Value
        Target.Offset(0, -4).Value = fnd.Offset(0, 2).Value
        
        ' Calculating the price based on quantity
        Dim quantity As Double
        If IsEmpty(Target.Offset(0, -3).Value) Then
            quantity = 1
        Else
            quantity = Target.Offset(0, -3).Value
        End If
        Target.Offset(0, -5).Value = Target.Offset(0, -4).Value * quantity
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet
    Set ws = Sh
    Call Workbook_SheetChange(ws, ws.Cells(1, 1)) ' Trigger the code for the new sheet
End Sub

Explanation:

1. We check the quantity in column E using the Target.Offset(0, -3).Value.
2. If it's empty, we assume the quantity is 1.
3. If there's a value in column E, we multiply the original price (from column D) with the quantity.
4. The calculated price is then put in column C using Target.Offset(0, -5).Value.

This should meet your requirement.

 

[Agnarr]

After some twigging i made it work and thank you very much.
My question is if i can add the quantity later and have the price change

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Sh.Range("g:g")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    Dim fnd As Range
    Set fnd = Sheets("codes").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        ' Copying name and original price
        Target.Offset(0, -5).Value = fnd.Offset(0, 1).Value
        Target.Offset(0, -3).Value = fnd.Offset(0, 2).Value
        
        ' Calculating the price based on quantity
        Dim quantity As Double
        If IsEmpty(Target.Offset(0, -2).Value) Then
            quantity = 1
        Else
            quantity = Target.Offset(0, -2).Value
        End If
        Target.Offset(0, -4).Value = Target.Offset(0, -3).Value * quantity
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet
    Set ws = Sh
    Call Workbook_SheetChange(ws, ws.Cells(1, 1)) ' Trigger the code for the new sheet
End Sub