Macro to calculate with short cut key

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows
Hello

I am trying to record a macro as per my requirement. The cells in grey color are to be displayed by a short cut key. The columns D to X are always in the same order but they may be from G to AA or C to W in some cases, but the order of the columns is the same where the calculation is applied. The short cut key should display the result in the cell selected as per the cursor with the calculation as above. When I created a macro displayed correctly but if I enter a column before D then the formula doesn’t work correctly. The macro records the cell numbers. Instead of cell numbers is there a way for the macro to select the above cell in all cases.
Query for Calculation by macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1ABCDEFGHIJKLMNOP5121828
23090.50254.00719.00794.00845.006.356.3543.1443.1471.4671.46118.30118.30
31518.20140.00998.00198.003.503.5059.8859.8827.7227.72
41894.19483.00504.00619.0012.0812.0845.3645.3686.6686.66
51709.50214.00368.00838.005.355.3522.0822.08117.32117.32
61976.87701.00835.00259.0017.5317.5350.1050.1023.3123.31
71880.08537.00180.00833.0032.2232.2216.2016.20116.62116.62
81579.11715.00702.0017.8817.8863.1863.18
92307.36526.00409.00931.00155.0013.1513.1524.5424.5483.7983.7921.7021.70
10977.04828.0074.5274.52
11729.54103.00475.0018.54133.00
121221.78434.00684.0021.7082.08
13945.00900.0045.00
141226.40560.00570.0028.0068.40
15998.40780.00218.40
161796.87719.00814.0035.95227.92
17887.07395.00369.0019.75103.32
182278.17449.00628.00862.0022.4575.36241.36
19
20Grand Total27016.083033.003866.004198.003488.003457.001882.00103.003300.0075.8375.83231.96231.96377.82377.82488.32488.32172.85225.8418.54924.00
2175.83231.96377.82488.32172.85225.8418.54924.00
22--------
23
Sheet1
Cell Formulas
RangeFormula
D20:X20D20=SUM(D2:D19)
E21E21=E20*2.5%
F21F21=F20*6%
G21G21=G20*9%
H21H21=H20*14%
I21I21=I20*5%
J21J21=J20*12%
K21K21=K20*18%
L21L21=L20*28%
E22E22=E21-M20
F22F22=F21-O20
G22G22=G21-Q20
H22H22=H21-S20
I22:L22I22=I21-U20
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows
Show your macro.
Hi Glenn, This is my macro.

Sub GST_Match()
'
' GST Macro
' Press control + small letter g to run the calculation.
'
' Keyboard Shortcut: Ctrl+g
'
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
Range("E22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[8]"
Range("F21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("F22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[9]"
Range("G21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*9%"
Range("G22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[10]"
Range("H21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*14%"
Range("H22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[11]"
Range("I21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*5%"
Range("I22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("J21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*12%"
Range("K21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*18%"
Range("L21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*28%"
Range("I22").Select
Selection.AutoFill Destination:=Range("I22:L22"), Type:=xlFillDefault
Range("I22:L22").Select
Range("E21").Select
End Sub
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows
This is what I have to do to the data I receive. As it is a regular routine, I thought of creating a macro which will finish this work in seconds.

I am sending the original data file along with the data with calculation. The yellow data is the data caclulated manually below the end of the last row of "Gross" column and dragged till the R/o column. I will see that the columns are arranged the same way as the code but the rows may vary from 800 - 2000 lines. That is the most difficult part for me to create the macro. If I calculate in the 2000th row then there would be lot of blank rows.
Query for Calculation by macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Original Data >>>>>GrossABCDEFGHIJKLMNOPQRSTR/o
23090.00254.00719.00794.00845.006.356.3543.1443.1471.4671.46118.30118.30-0.50
31518.00140.00998.00198.003.503.5059.8859.8827.7227.72-0.20
41894.00483.00504.00619.0012.0812.0845.3645.3686.6686.66-0.19
51710.00214.00368.00838.005.355.3522.0822.08117.32117.320.50
61977.00701.00835.00259.0017.5317.5350.1050.1023.3123.310.13
71880.00537.00180.00833.0032.2232.2216.2016.20116.62116.62-0.08
81580.00715.00702.0017.8817.8863.1863.180.89
92307.00526.00409.00931.00155.0013.1513.1524.5424.5483.7983.7921.7021.70-0.36
10977.00828.0074.5274.52-0.04
11730.00103.00475.0018.54133.000.46
121222.00434.00684.0021.7082.080.22
13945.00900.0045.000.00
141226.00560.00570.0028.0068.40-0.40
15998.00780.00218.40-0.40
161797.00719.00814.0035.95227.920.13
17887.00395.00369.0019.75103.32-0.07
182278.00449.00628.00862.0022.4575.36241.36-0.17
Original Data
Cell Formulas
RangeFormula
AA2:AA18AA2=F2-SUM(G2:Z2)


Query for Calculation by macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Data with Manual calculation>>>>>GrossABCDEFGHIJKLMNOPQRSTR/o
23090.00254.00719.00794.00845.006.356.3543.1443.1471.4671.46118.30118.30-0.50
31518.00140.00998.00198.003.503.5059.8859.8827.7227.72-0.20
41894.00483.00504.00619.0012.0812.0845.3645.3686.6686.66-0.19
51710.00214.00368.00838.005.355.3522.0822.08117.32117.320.50
61977.00701.00835.00259.0017.5317.5350.1050.1023.3123.310.13
71880.00537.00180.00833.0032.2232.2216.2016.20116.62116.62-0.08
81580.00715.00702.0017.8817.8863.1863.180.89
92307.00526.00409.00931.00155.0013.1513.1524.5424.5483.7983.7921.7021.70-0.36
10977.00828.0074.5274.52-0.04
11730.00103.00475.0018.54133.000.46
121222.00434.00684.0021.7082.080.22
13945.00900.0045.000.00
141226.00560.00570.0028.0068.40-0.40
15998.00780.00218.40-0.40
161797.00719.00814.0035.95227.920.13
17887.00395.00369.0019.75103.32-0.07
182278.00449.00628.00862.0022.4575.36241.36-0.17
19
20Grand Total27,016.003,033.003,866.004,198.003,488.003,457.001,882.00103.003,300.0075.8375.83231.96231.96377.82377.82488.32488.32172.85225.8418.54924.00-0.08
2175.83231.96377.82488.32172.85225.8418.54924.00
22--------
23
Data with Manual calculation
Cell Formulas
RangeFormula
AA2:AA18AA2=F2-SUM(G2:Z2)
F20:AA20F20=SUM(F2:F19)
G21G21=G20*2.5%
H21H21=H20*6%
I21I21=I20*9%
J21J21=J20*14%
K21K21=K20*5%
L21L21=L20*12%
M21M21=M20*18%
N21N21=N20*28%
G22G22=G21-O20
H22H22=H21-Q20
I22I22=I21-S20
J22:N22J22=J21-V20
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

This is what I have to do to the data I receive. As it is a regular routine, I thought of creating a macro which will finish this work in seconds.

I am sending the original data file along with the data with calculation. The yellow data is the data caclulated manually below the end of the last row of "Gross" column and dragged till the R/o column. I will see that the columns are arranged the same way as the code but the rows may vary from 800 - 2000 lines. That is the most difficult part for me to create the macro. If I calculate in the 2000th row then there would be lot of blank rows.
Query for Calculation by macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Original Data >>>>>GrossABCDEFGHIJKLMNOPQRSTR/o
23090.00254.00719.00794.00845.006.356.3543.1443.1471.4671.46118.30118.30-0.50
31518.00140.00998.00198.003.503.5059.8859.8827.7227.72-0.20
41894.00483.00504.00619.0012.0812.0845.3645.3686.6686.66-0.19
51710.00214.00368.00838.005.355.3522.0822.08117.32117.320.50
61977.00701.00835.00259.0017.5317.5350.1050.1023.3123.310.13
71880.00537.00180.00833.0032.2232.2216.2016.20116.62116.62-0.08
81580.00715.00702.0017.8817.8863.1863.180.89
92307.00526.00409.00931.00155.0013.1513.1524.5424.5483.7983.7921.7021.70-0.36
10977.00828.0074.5274.52-0.04
11730.00103.00475.0018.54133.000.46
121222.00434.00684.0021.7082.080.22
13945.00900.0045.000.00
141226.00560.00570.0028.0068.40-0.40
15998.00780.00218.40-0.40
161797.00719.00814.0035.95227.920.13
17887.00395.00369.0019.75103.32-0.07
182278.00449.00628.00862.0022.4575.36241.36-0.17
Original Data
Cell Formulas
RangeFormula
AA2:AA18AA2=F2-SUM(G2:Z2)


Query for Calculation by macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Data with Manual calculation>>>>>GrossABCDEFGHIJKLMNOPQRSTR/o
23090.00254.00719.00794.00845.006.356.3543.1443.1471.4671.46118.30118.30-0.50
31518.00140.00998.00198.003.503.5059.8859.8827.7227.72-0.20
41894.00483.00504.00619.0012.0812.0845.3645.3686.6686.66-0.19
51710.00214.00368.00838.005.355.3522.0822.08117.32117.320.50
61977.00701.00835.00259.0017.5317.5350.1050.1023.3123.310.13
71880.00537.00180.00833.0032.2232.2216.2016.20116.62116.62-0.08
81580.00715.00702.0017.8817.8863.1863.180.89
92307.00526.00409.00931.00155.0013.1513.1524.5424.5483.7983.7921.7021.70-0.36
10977.00828.0074.5274.52-0.04
11730.00103.00475.0018.54133.000.46
121222.00434.00684.0021.7082.080.22
13945.00900.0045.000.00
141226.00560.00570.0028.0068.40-0.40
15998.00780.00218.40-0.40
161797.00719.00814.0035.95227.920.13
17887.00395.00369.0019.75103.32-0.07
182278.00449.00628.00862.0022.4575.36241.36-0.17
19
20Grand Total27,016.003,033.003,866.004,198.003,488.003,457.001,882.00103.003,300.0075.8375.83231.96231.96377.82377.82488.32488.32172.85225.8418.54924.00-0.08
2175.83231.96377.82488.32172.85225.8418.54924.00
22--------
23
Data with Manual calculation
Cell Formulas
RangeFormula
AA2:AA18AA2=F2-SUM(G2:Z2)
F20:AA20F20=SUM(F2:F19)
G21G21=G20*2.5%
H21H21=H20*6%
I21I21=I20*9%
J21J21=J20*14%
K21K21=K20*5%
L21L21=L20*12%
M21M21=M20*18%
N21N21=N20*28%
G22G22=G21-O20
H22H22=H21-Q20
I22I22=I21-S20
J22:N22J22=J21-V20

This is the macro I recorded while doing the calculation.
Option Explicit

Sub Test()
'
' Test Macro
'

'
Range("F1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.End(xlDown).Select
Range("F20").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-18]C:R[-1]C)"
Range("F20").Select
Selection.AutoFill Destination:=Range("F20:AA20"), Type:=xlFillDefault
Range("F20:AA20").Select
Selection.Style = "Comma"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("G21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
Range("H21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*6%"
Range("I21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*9%"
Range("J21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*14%"
Range("K21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*5%"
Range("L21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*12%"
Range("M21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*18%"
Range("N21").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*28%"
Range("G22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[8]"
Range("H22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[9]"
Range("I22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[10]"
Range("J22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("K22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("L22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("M22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("N22").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C[12]"
Range("G21:N22").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
Range("G21").Select
End Sub
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Have a look at this:


VBA Code:
Sub GST_Match()
'
' GST Macro
' Press control + small letter g to run the calculation.
'
' Keyboard Shortcut: Ctrl+g

Application.Calculation = xlCalculationManual
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
For ifill = 1 To 8
ActiveCell.Offset(1, ifill - 1).FormulaR1C1 = "=R[-1]C-R[-2]C[" & Application.Min(ifill, 5) + 7 & "]"
Next
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[-1]C*6%"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[-1]C*9%"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[-1]C*14%"
ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[-1]C*5%"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[-1]C*12%"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[-1]C*18%"
ActiveCell.Offset(0, 7).FormulaR1C1 = "=R[-1]C*28%"
Application.Calculation = xlCalculationAutomatic
End Sub
 
Solution

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows
Have a look at this:


VBA Code:
Sub GST_Match()
'
' GST Macro
' Press control + small letter g to run the calculation.
'
' Keyboard Shortcut: Ctrl+g

Application.Calculation = xlCalculationManual
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
For ifill = 1 To 8
ActiveCell.Offset(1, ifill - 1).FormulaR1C1 = "=R[-1]C-R[-2]C[" & Application.Min(ifill, 5) + 7 & "]"
Next
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[-1]C*6%"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[-1]C*9%"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[-1]C*14%"
ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[-1]C*5%"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[-1]C*12%"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[-1]C*18%"
ActiveCell.Offset(0, 7).FormulaR1C1 = "=R[-1]C*28%"
Application.Calculation = xlCalculationAutomatic
End Sub
Glenn, It worked perfectly. Can you add a line in the beginning, active cell F2 and then add a code to run the macro to the last empty call and then go to right empty cell and then I can run the macro. This way if the number of rows are different then to the code will run.
 
Last edited:

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
673
Office Version
  1. 2019
Platform
  1. Windows
Have a look at this:


VBA Code:
Sub GST_Match()
'
' GST Macro
' Press control + small letter g to run the calculation.
'
' Keyboard Shortcut: Ctrl+g

Application.Calculation = xlCalculationManual
ActiveCell.FormulaR1C1 = "=R[-1]C*2.5%"
For ifill = 1 To 8
ActiveCell.Offset(1, ifill - 1).FormulaR1C1 = "=R[-1]C-R[-2]C[" & Application.Min(ifill, 5) + 7 & "]"
Next
ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[-1]C*6%"
ActiveCell.Offset(0, 2).FormulaR1C1 = "=R[-1]C*9%"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=R[-1]C*14%"
ActiveCell.Offset(0, 4).FormulaR1C1 = "=R[-1]C*5%"
ActiveCell.Offset(0, 5).FormulaR1C1 = "=R[-1]C*12%"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=R[-1]C*18%"
ActiveCell.Offset(0, 7).FormulaR1C1 = "=R[-1]C*28%"
Application.Calculation = xlCalculationAutomatic
End Sub
Thank You very much Glenn. I will post the addititional requirement in a new post.
 

Forum statistics

Threads
1,143,637
Messages
5,719,971
Members
422,253
Latest member
frankie2016tata

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