Need help in adding formulas to existing code

jocote46

New Member
Joined
May 11, 2009
Messages
43
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Just guessing...
VBA Code:
Range("AC2") = [Y2] - [Z2]
Range("AD2") = [N2] / [R2]
Range("AE2") = [AC2] * [AD2]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
'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

New Member
Joined
May 11, 2009
Messages
43
Thank you for your feedback, i figured it out. this is the correct way to add the mathematical operations to my existing code.

VBA 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

New Member
Joined
May 11, 2009
Messages
43

ADVERTISEMENT

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
VBA 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

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
May 11, 2009
Messages
43
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.
VBA 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

Well-known Member
Joined
Oct 27, 2005
Messages
19,701
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Bit shorter
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top