Need to record values from if Statement. Dont know how to code VBA. if you can, please send me the code

Rish 0
Viewed 46

image here

As you can see the , the column under "Aep cost" contains an if statement that matches the month to the month entered in C2 to extract values from "total bill" for the relevant bill. But I lose the values when the month in C2 is changed. I need to record the values into the L column in such a way that they retain those values and don't go back to 0 when the if statement under "aep cost" is not satisfied. They only change if the "total bill" amount changes. I hope the question is clear.

excel-formula vba excel
1 Answers

Firstly, remove the formulas in the range K12:K23. You can't use formulas here as they will update automatically. Leave the 2 d.p. formatting in place though.

The following macro (don't forget workbooks with macros need to be saved as .xlsm, not .xlsx!) will place the value found in K8 into the relevant cell of the K12:K23 table based on the month value in C2.

Sub update_AEP_cost_for_month()

    Dim mth As Long, ttl_bill As Double
    
    With ActiveSheet
        mth = .Range("C2").Value
        ttl_bill = .Range("K8").Value
        .Range("K12:K23").Cells(mth).Value = ttl_bill
    End With

End Sub

What you need to decide is how to trigger the running of this macro:

I'd probably suggest the simplest way would be to add a shape/button in the J4 area, label it 'Update' and have the user click it when the update should run.

An alternative would be to use a calculation event - but that might cause problems if K8 and C2 aren't always perfectly in sync.

CLR0
Related