Excel - VBA - Autosum Selected cell

C

CrashOD

Board Regular
Joined
Feb 5, 2019
Messages
118
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
Sub Try()

'select cell B 10,000
ActiveSheet.Range("B10000").Select

' goes up to last drawer posted if cell is empty in B
Selection.End(xlUp).Select
Selection.End(xlUp).Select

' highlights cells below for sorting
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select

' Sorts D then B
Selection.Sort Range("D:D"), xlAscending, Range("B:B"), , xlAscending

'select cell C 10,000
ActiveSheet.Range("C10000").Select

' goes up to last drawer posted if cell is empty
Selection.End(xlUp).Offset(1, 0).Select

' Inserts new line + make bold
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Font.Bold = True
Rows(ActiveCell.Row).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With


Cells(ActiveCell.Row, "I").Select
Cells(ActiveCell.Row, "I").AutoSum 'Has error


End Sub

Book1.xlsx
ABCDEFGHIJ
2912YEAGER CHARLES B 017002459/21/2022P379#11,103.151,125.671,238.25
29139/21/2022 #11,125.671,238.25
2914MENSCH DONALD A/WAGNER CONNIE A017001399/23/2022P82895414.23422.69464.97
2915MENSCH DONALD A/WAGNER CONNIE A017001409/23/2022P82896ADDRESS: 814 N COAL ST, SHAMOKIN, PA 17872 - SENT 3/8/2022 VAF396.79404.90445.41
29169/23/2022827.59910.38
2917ZAWALICK JOHN L/ROSEMARIE012007309/26/2022P408043.6044.5048.95
291829 N JONES LLC 017000019/26/2022P10905981.081,001.101,101.21
29199/26/20221,045.601,150.16
2920DEPKA CASIMIR S JR/ROBERTA 010000939/28/2022P$462.20471.63518.80
2921PORTO JUDITH D 010003399/28/2022P6265885.13903.20993.52
2922FERRIER JAMES FRANCIS011000699/28/2022PPG 9881590588.64600.66660.73
2923CLARK KEVIN ANTHONY014000599/28/2022P2873566.83578.41636.26
29249/28/20222,553.902,809.31
2925REBUCK NORENE011001819/30/2022P1805318.29324.80357.29
29269/30/2022324.80357.29
2927PAWELCZYK DANIEL A JR/MELINDA A0130050210/4/2022P$#2392.43400.44440.49
292810/4/2022 #2400.44440.49
2929SAMPSON ROSEMARIE0110019510/7/2022PMO 28073245436.03444.93489.43
2930LAHOUARI BELMAHI 0120033310/7/2022P$388.06395.99435.60
2931BARSHINGER MARK (NEW OWNER: TAYLOR JOHNNIE D JR)0130002810/7/2022PMO 28222364632ADDRESS: 2925 SILVER HILL AVE, GWYNN OAK, MD 21207 164.83168.18185.02
2932DOMINICAN INNOVATIONS INC0130012410/7/2022PCASHIERS CHECK 1683704054370.63378.20416.02
2933DOMINICAN INNOVATIONS INC0130012510/7/2022PCASHIERS CHECK 1683704054370.63378.20416.02
2934DOMINICAN INNOVATIONS INC0140008010/7/2022PCASHIERS CHECK 1683704054405.50413.79455.18
2935DOMINICAN INNOVATIONS INC0180004910/7/2022PCASHIERS CHECK 1683704054366.27373.74411.11
2936DOMINICAN INNOVATIONS INC0180005010/7/2022PCASHIERS CHECK 1683704054 & 1683704055353.16360.39396.44
293710/7/20222,913.423,204.82
SCHOOL 2022
Cell Formulas
RangeFormula
I2928:J2928,I2926:J2926,I2913:J2913I2913=SUM(I2912)
I2919:J2919,I2916:J2916I2916=SUM(I2914:I2915)
I2924:J2924I2924=SUM(I2920:I2923)
I2937:J2937I2937=SUM(I2929:I2936)


1. im looking for command to autosum "i" at min.
as you can see from my code the last part i get "i" selected but can not get it to auto sum. looking for this separate so if i decided I can use the code for something else that is not date orientated.

2. is there away say it is between a certain date range. say
March - April when Macro Activated it will autosum "H" and "I",
May - June when Macro Activated it will autosum "I",
July - Dec when Macro Activated it will autosum "I" & "J":


thanks so much!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
CrashOD said:
Sub Try()

'select cell B 10,000
ActiveSheet.Range("B10000").Select

' goes up to last drawer posted if cell is empty in B
Selection.End(xlUp).Select
Selection.End(xlUp).Select

' highlights cells below for sorting
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select

' Sorts D then B
Selection.Sort Range("D:D"), xlAscending, Range("B:B"), , xlAscending

'select cell C 10,000
ActiveSheet.Range("C10000").Select

' goes up to last drawer posted if cell is empty
Selection.End(xlUp).Offset(1, 0).Select

' Inserts new line + make bold
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Font.Bold = True
Rows(ActiveCell.Row).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With


Cells(ActiveCell.Row, "I").Select
Cells(ActiveCell.Row, "I").AutoSum 'Has error


End Sub

Book1.xlsx
ABCDEFGHIJ
2912YEAGER CHARLES B 017002459/21/2022P379#11,103.151,125.671,238.25
29139/21/2022 #11,125.671,238.25
2914MENSCH DONALD A/WAGNER CONNIE A017001399/23/2022P82895414.23422.69464.97
2915MENSCH DONALD A/WAGNER CONNIE A017001409/23/2022P82896ADDRESS: 814 N COAL ST, SHAMOKIN, PA 17872 - SENT 3/8/2022 VAF396.79404.90445.41
29169/23/2022827.59910.38
2917ZAWALICK JOHN L/ROSEMARIE012007309/26/2022P408043.6044.5048.95
291829 N JONES LLC 017000019/26/2022P10905981.081,001.101,101.21
29199/26/20221,045.601,150.16
2920DEPKA CASIMIR S JR/ROBERTA 010000939/28/2022P$462.20471.63518.80
2921PORTO JUDITH D 010003399/28/2022P6265885.13903.20993.52
2922FERRIER JAMES FRANCIS011000699/28/2022PPG 9881590588.64600.66660.73
2923CLARK KEVIN ANTHONY014000599/28/2022P2873566.83578.41636.26
29249/28/20222,553.902,809.31
2925REBUCK NORENE011001819/30/2022P1805318.29324.80357.29
29269/30/2022324.80357.29
2927PAWELCZYK DANIEL A JR/MELINDA A0130050210/4/2022P$#2392.43400.44440.49
292810/4/2022 #2400.44440.49
2929SAMPSON ROSEMARIE0110019510/7/2022PMO 28073245436.03444.93489.43
2930LAHOUARI BELMAHI 0120033310/7/2022P$388.06395.99435.60
2931BARSHINGER MARK (NEW OWNER: TAYLOR JOHNNIE D JR)0130002810/7/2022PMO 28222364632ADDRESS: 2925 SILVER HILL AVE, GWYNN OAK, MD 21207 164.83168.18185.02
2932DOMINICAN INNOVATIONS INC0130012410/7/2022PCASHIERS CHECK 1683704054370.63378.20416.02
2933DOMINICAN INNOVATIONS INC0130012510/7/2022PCASHIERS CHECK 1683704054370.63378.20416.02
2934DOMINICAN INNOVATIONS INC0140008010/7/2022PCASHIERS CHECK 1683704054405.50413.79455.18
2935DOMINICAN INNOVATIONS INC0180004910/7/2022PCASHIERS CHECK 1683704054366.27373.74411.11
2936DOMINICAN INNOVATIONS INC0180005010/7/2022PCASHIERS CHECK 1683704054 & 1683704055353.16360.39396.44
293710/7/20222,913.423,204.82
SCHOOL 2022
Cell Formulas
RangeFormula
I2928:J2928,I2926:J2926,I2913:J2913I2913=SUM(I2912)
I2919:J2919,I2916:J2916I2916=SUM(I2914:I2915)
I2924:J2924I2924=SUM(I2920:I2923)
I2937:J2937I2937=SUM(I2929:I2936)


1. im looking for command to autosum "i" at min.
as you can see from my code the last part i get "i" selected but can not get it to auto sum. looking for this separate so if i decided I can use the code for something else that is not date orientated.

2. is there away say it is between a certain date range. say
March - April when Macro Activated it will autosum "H" and "I",
May - June when Macro Activated it will autosum "I",
July - Dec when Macro Activated it will autosum "I" & "J":


thanks so much!
Click to expand...
1. im looking for command to autosum "i" at min.
as you can see from my code the last part i get "i" selected but can not get it to auto sum. looking for this separate so if i decided I can use the code for something else that is not date orientated.

2. is there away say it is between a certain date range. say or if it can read the D Colum
(In Colum D up on cell from current row)
(D shows D) or date on current line is between March - April when Macro Activated it will autosum "H" and "I",
(D shows F) or date on current line is between May - June when Macro Activated it will autosum "I",
(D shows P) or date on current line is between July - Dec when Macro Activated it will autosum "I" & "J":

The Colum A with date sometime has other info after it like
10/7/2022 - partial pay
10/7/2022 #1
ect
 
Upvote 0
CrashOD said:
1. im looking for command to autosum "i" at min.
as you can see from my code the last part i get "i" selected but can not get it to auto sum. looking for this separate so if i decided I can use the code for something else that is not date orientated.

2. is there away say it is between a certain date range. say or if it can read the D Colum
(In Colum D up on cell from current row)
(D shows D) or date on current line is between March - April when Macro Activated it will autosum "H" and "I",
(D shows F) or date on current line is between May - June when Macro Activated it will autosum "I",
(D shows P) or date on current line is between July - Dec when Macro Activated it will autosum "I" & "J":

The Colum A with date sometime has other info after it like
10/7/2022 - partial pay
10/7/2022 #1
ect
Click to expand...
Question 1. I thought I had this figured out but i dont.

' Selects Cell "I" in same row
Cells(ActiveCell.Row, "I").Select

'AutoSums current Cell
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Select all rows from active cell (selected cell) on table to 2nd last row
Replies
3
Views
68
rlv01
R
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
F
  • Question
From slicer selection, populate the selected item to a cell
Replies
9
Views
180
FaezMH
F
C
  • Solved
Excel 2016 - VBA - Select sheets if data
Replies
1
Views
355
CrashOD
C
T
  • Question
Linear Extrapolation / Forecast for upcoming months
Replies
0
Views
359
Trepidatioussaurus
T

Forum statistics

Threads
1,215,701
Messages
6,126,292
Members
449,308
Latest member
VerifiedBleachersAttendee

Share this page

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
Back
Top