Need help in adding formulas to existing code

[jocote46]

Hello, i have created this VBA macro and I would like to add some mathematical operations to it, see code at the bottom for what i'm trying to do but not sure what is the correct way to do it. thanks in advance for your assistance.

Sub InsertColumnGP()

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Range("AA1:AD1").Interior.Color = ColorConstants.vbYellow
Range("AA1:AD1").Font.Bold = True
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"
Range("AB2").Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Sheet2'!$A$2:$B$300,2,0)"

    With ActiveSheet
            .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AB2").AutoFill .Range("AB2:AB" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            'add additional columns here
    End With
    
    With Range("G2:G" & Cells(Rows.Count, "F").End(xlUp).Row)
.NumberFormat = "General"
 .Value = .Value
End With

'Need to add the following mathematical operations to this macro
Range("AC2") = Sum(Y2 - Z2)
Range("AD2") = Sum(N2 / R2)
Range("AE2") = Sum(AC2 * AD2)



End Sub

 

[GWteB]

Just guessing...

Range("AC2") = [Y2] - [Z2]
Range("AD2") = [N2] / [R2]
Range("AE2") = [AC2] * [AD2]

 

[Joe4]

'Need to add the following mathematical operations to this macro
Range("AC2") = Sum(Y2 - Z2)
Range("AD2") = Sum(N2 / R2)
Range("AE2") = Sum(AC2 * AD2)

Note that the SUM function is totally unnecessary and serves no purpose in these formulas.
You use the SUM formula to add up a range of values. You are trying to do subtraction, division, and multiplication.
Adding the SUM function doesn't change the value, but serves no purpose and there is no need to add it in to these formulas.

I am not sure why so many people use it with mathematical formulas that are not sums at all.

 

[jocote46]

Thank you for your feedback, i figured it out. this is the correct way to add the mathematical operations to my existing code.

Range("AC2").Value = Range("Y2").Value - Range("Z2").Value
Range("AD2").Value = Range("N2").Value / Range("R2").Value
Range("AE2").Value = Range("AC2").Value * Range("AD2").Value

 

[jocote46]

I'm back, so the calculations work, however i need to have that formula (Y2-N2) to be copy down to last row. and the same for the formulas on AD2 and AE2. this is what i have so far

Range("AC2").Value = Range("Y2").Value - Range("Z2").Value
Range("AD2").Value = Range("N2").Value / Range("R2").Value
Range("AE2").Value = Range("AC2").Value * Range("AD2").Value

With ActiveSheet
            .Range("AC2").AutoFill .Range("AC2:AC" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AD2").AutoFill .Range("AD2:AD" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AE2").AutoFill .Range("AE2:AE" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            'add additional columns here
    End With

 

[GWteB]

For the record, I think I should note that you are probably not attempting to copy a formula rather the outcome of a calculation.
Would you care to post a sample of your current worksheet and an example of the desired result? Please use xl2bb, see my signature.

 

[jocote46]

I figured it out! Yay! Woot...so happy. yes, i was trying to copy the outcome of a calculation. see below code which i added to existing code.

Dim ws As Worksheet
Set ws = ActiveSheet

'Insert Column to next to the Account number, to the right of Column W
ActiveSheet.Select
Range("AA1").Value = "BU"
Range("AB1").Value = "Amount"
Range("AC1").Value = "Portion"
Range("AD1").Value = "Balance"
Range("AA1:AD1").Interior.Color = ColorConstants.vbYellow
Range("AA1:AD1").Font.Bold = True
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Columns("G").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2").Formula = "=Left(W2, 10)"
Range("AB2").Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Sheet2'!$A$2:$B$300,2,0)"
Range("AC2").Formula = "=Y2-Z2"
Range("AD2").Formula = "=N2/R2"
Range("AE2").Formula = "=AC2*AD2"

    With ActiveSheet
            .Range("X2").AutoFill .Range("X2:X" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AB2").AutoFill .Range("AB2:AB" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AC2").AutoFill .Range("AC2:AC" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AD2").AutoFill .Range("AD2:AD" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            .Range("AE2").AutoFill .Range("AE2:AE" & .Cells(.Rows.Count, "W").End(xlUp).Row)
            'add additional columns here
    End With
    
    With Range("G2:G" & Cells(Rows.Count, "F").End(xlUp).Row)
.NumberFormat = "General"
 .Value = .Value
End With




End Sub

Thank you again , this post has been solved.

 

[Michael M]

Bit shorter

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "W").End(xlUp).Row
    With Range("AA1:AD1")
        .Value = [{"BU","Amount","Portion","Balance"}]
        .Interior.Color = vbYellow
        .Font.Bold = True
    End With
Columns("X:X").Insert
Columns("X").NumberFormat = "General"
Range("X1").Value = "Sub Account"
Range("X2:X" & lr).Formula = "=Left(W2, 10)"
Range("AB2:AB" & lr).Formula = "=VLOOKUP(X2,'[Adaptive Level Hierarchy by Geoff 8.21.20_WA.xlsx]Sheet2'!$A$2:$B$300,2,0)"
Range("AC2:AC" & lr).Formula = "=Y2-Z2"
Range("AD2:AD" & lr).Formula = "=N2/R2"
Range("AE2:AE" & lr).Formula = "=AC2*AD2"
    With Range("G2:G" & Cells(Rows.Count, "F").End(xlUp).Row)
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub