Excel Calculation using VBA

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Hi.. does anyone know how to calculate the values automatically using VBA..? For example for Cell Z13, the calculation is Q13+Q14, and for Z14, the calculation is Q15+Q16, and so on...
I've been thinking how to do the calculation but still cant figure it out. Could anyone send some help here, please?

1608799865023.png
 

Attachments

  • 1608799058089.png
    1608799058089.png
    48.4 KB · Views: 6

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
You don't need to create a formula, simply go click on a cell in your source table, then click on Insert Pivottable on the Insert Menu. Then choose dates as the Row component and put Products in the Values area.

It'll give you exactly what you need.

HTH
 

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
You don't need to create a formula, simply go click on a cell in your source table, then click on Insert Pivottable on the Insert Menu. Then choose dates as the Row component and put Products in the Values area.

It'll give you exactly what you need.

HTH
Oh, why didn't I think of that one. Thanks pjmorris!
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
894
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Any Way
VBA Code:
Sub test()
    Dim i, ii
    ii = 13
    For i = 13 To Cells(Rows.Count, "Q").End(xlUp).Row Step 2
        Range("Z" & ii) = Range("Q" & i) + Range("Q" & i + 1)
        ii = ii + 1
    Next
End Sub
 

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
Any Way
VBA Code:
Sub test()
    Dim i, ii
    ii = 13
    For i = 13 To Cells(Rows.Count, "Q").End(xlUp).Row Step 2
        Range("Z" & ii) = Range("Q" & i) + Range("Q" & i + 1)
        ii = ii + 1
    Next
End Sub
Hi Mohadin,

Thank you very much! Very much appreciated :)
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
282

ADVERTISEMENT

Try this in Z13:

=SUMIF($O$13:$O$30,$I13,INDEX($Q$13:$T$30,0,MATCH(Z$12,$Q$12:$T$12,0)))
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
what about
Yyy.xlsx
ABCDEFGHIJKLMN
1 Production Production
2DateShiftProduct 1 ProductProduct3 Product4TotalDateProductl Product2 Product3 Product4Total
301-OctDay1735001300013003900191700 01-Oct2989003290032007500342500
402-OctNight1254001990019003600150800 02-Oct2848002850039006400323600
502-OctDay1606001490017005200182400 03-Oct2642002390038003000294900
602-OctNight1242001360022001200141200 04-Oct2259004150045008700280600
703-OctDay1306004009000131900 05-Oct1596003180040005600201000
804-OctNight1336002350029003000163000 06-Oct89300820013004500103300
904-OctDay1196002150025003300146900 07-Oct3337002320054004800367100
1005-OctNight1063002000020005400133700 08-Oct4180004040044008300471100
1105-OctDay65000172001400440088000 09-Oct2548002790044004800291900
1205-OctNight946001460026001200113000
1306-OctDay615002400050064400
1406-OctNight2780058001300400038900
1507-OctDay2087001280038002900228200
1607-OctNight1250001040016001900138900
1708-OctDay1974002450026003400227900
1808-OctNight2206001590018004900243200
1909-OctDay934001560022003100114300
2010-OctNight1614001230022001700177600
21
Sheet1
Cell Formulas
RangeFormula
J3:M11J3=SUM(OFFSET(INDIRECT(ADDRESS(((ROW()-ROW($J$3))*2)+ROW($C$3),COLUMN(C3))),0,0,2,1))
N3:N11N3=SUM(J3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:N11Expression=MOD(ROW()-ROW($I$3)+1,2)<>0textNO
A3:G20Expression=MOD(ROW()-ROW($A$3)+1,2)<>0textNO


Yyy.xlsx
ABCDEFGHIJKLMN
1
2
3
4
5
6
7
8
9
10
11 Production Production
12DateShiftProduct 1 ProductProduct3 Product4TotalDateProductl Product2 Product3 Product4Total
1301-OctDay1735001300013003900191700 01-Oct2989003290032007500342500
1402-OctNight1254001990019003600150800 02-Oct2848002850039006400323600
1502-OctDay1606001490017005200182400 03-Oct2642002390038003000294900
1602-OctNight1242001360022001200141200 04-Oct2259004150045008700280600
1703-OctDay1306004009000131900 05-Oct1596003180040005600201000
1804-OctNight1336002350029003000163000 06-Oct89300820013004500103300
1904-OctDay1196002150025003300146900 07-Oct3337002320054004800367100
2005-OctNight1063002000020005400133700 08-Oct4180004040044008300471100
2105-OctDay65000172001400440088000 09-Oct2548002790044004800291900
2205-OctNight946001460026001200113000
2306-OctDay615002400050064400
2406-OctNight2780058001300400038900
2507-OctDay2087001280038002900228200
2607-OctNight1250001040016001900138900
2708-OctDay1974002450026003400227900
2808-OctNight2206001590018004900243200
2909-OctDay934001560022003100114300
3010-OctNight1614001230022001700177600
31
Sheet1
Cell Formulas
RangeFormula
J13:M21J13=SUM(OFFSET(INDIRECT(ADDRESS(((ROW()-ROW($J$13))*2)+ROW($C$13),COLUMN(C13))),0,0,2,1))
N13:N21N13=SUM(J13:M13)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I13:N21Expression=MOD(ROW()-ROW($I$13)+1,2)<>0textNO
A13:G30Expression=MOD(ROW()-ROW($A$13)+1,2)<>0textNO
 
Solution

oHoi99

New Member
Joined
Dec 20, 2020
Messages
18
Office Version
  1. 2013
Platform
  1. Windows
what about
Yyy.xlsx
ABCDEFGHIJKLMN
1 Production Production
2DateShiftProduct 1 ProductProduct3 Product4TotalDateProductl Product2 Product3 Product4Total
301-OctDay1735001300013003900191700 01-Oct2989003290032007500342500
402-OctNight1254001990019003600150800 02-Oct2848002850039006400323600
502-OctDay1606001490017005200182400 03-Oct2642002390038003000294900
602-OctNight1242001360022001200141200 04-Oct2259004150045008700280600
703-OctDay1306004009000131900 05-Oct1596003180040005600201000
804-OctNight1336002350029003000163000 06-Oct89300820013004500103300
904-OctDay1196002150025003300146900 07-Oct3337002320054004800367100
1005-OctNight1063002000020005400133700 08-Oct4180004040044008300471100
1105-OctDay65000172001400440088000 09-Oct2548002790044004800291900
1205-OctNight946001460026001200113000
1306-OctDay615002400050064400
1406-OctNight2780058001300400038900
1507-OctDay2087001280038002900228200
1607-OctNight1250001040016001900138900
1708-OctDay1974002450026003400227900
1808-OctNight2206001590018004900243200
1909-OctDay934001560022003100114300
2010-OctNight1614001230022001700177600
21
Sheet1
Cell Formulas
RangeFormula
J3:M11J3=SUM(OFFSET(INDIRECT(ADDRESS(((ROW()-ROW($J$3))*2)+ROW($C$3),COLUMN(C3))),0,0,2,1))
N3:N11N3=SUM(J3:M3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:N11Expression=MOD(ROW()-ROW($I$3)+1,2)<>0textNO
A3:G20Expression=MOD(ROW()-ROW($A$3)+1,2)<>0textNO


Yyy.xlsx
ABCDEFGHIJKLMN
1
2
3
4
5
6
7
8
9
10
11 Production Production
12DateShiftProduct 1 ProductProduct3 Product4TotalDateProductl Product2 Product3 Product4Total
1301-OctDay1735001300013003900191700 01-Oct2989003290032007500342500
1402-OctNight1254001990019003600150800 02-Oct2848002850039006400323600
1502-OctDay1606001490017005200182400 03-Oct2642002390038003000294900
1602-OctNight1242001360022001200141200 04-Oct2259004150045008700280600
1703-OctDay1306004009000131900 05-Oct1596003180040005600201000
1804-OctNight1336002350029003000163000 06-Oct89300820013004500103300
1904-OctDay1196002150025003300146900 07-Oct3337002320054004800367100
2005-OctNight1063002000020005400133700 08-Oct4180004040044008300471100
2105-OctDay65000172001400440088000 09-Oct2548002790044004800291900
2205-OctNight946001460026001200113000
2306-OctDay615002400050064400
2406-OctNight2780058001300400038900
2507-OctDay2087001280038002900228200
2607-OctNight1250001040016001900138900
2708-OctDay1974002450026003400227900
2808-OctNight2206001590018004900243200
2909-OctDay934001560022003100114300
3010-OctNight1614001230022001700177600
31
Sheet1
Cell Formulas
RangeFormula
J13:M21J13=SUM(OFFSET(INDIRECT(ADDRESS(((ROW()-ROW($J$13))*2)+ROW($C$13),COLUMN(C13))),0,0,2,1))
N13:N21N13=SUM(J13:M13)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I13:N21Expression=MOD(ROW()-ROW($I$13)+1,2)<>0textNO
A13:G30Expression=MOD(ROW()-ROW($A$13)+1,2)<>0textNO
Thanks a lot!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,584
Messages
5,597,026
Members
414,116
Latest member
sfullnet

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