How to use excel sum function using vba

irfananeeza

Active Member
Joined
Feb 15, 2008
Messages
334
Office Version
  1. 2010
Folks,
I've build the following code. It runs thru okay with sort. But gives trouble when get to ".Columns..." line. What am I doing wrong here.?
Sub macro()
With Range("A2:J5000")
.Sort key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlGuess, Ordercustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
.Columns(1).Find(what:="Non-ILEC", LookAt=xlWhole)
End With
End Sub

Secondly,
I build this code, it works perfectly. But can someone please have a look and show me how it can be rewritten with less code lines. Basically, I want to insert 3 lines and type total and sum value.
Sub macro()
Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).Offset(-2).Value = "Total BS ILEC Orders"
End Sub

Thirdly,
Can I use excel sum function in vba? if not...what is the alternative.?
How would I total "Total BS ILEC Orders" when the "Amount" column is G.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
irfananeeza,

Two out of three.

I've build the following code. It runs thru okay with sort. But gives trouble when get to ".Columns..." line. What am I doing wrong here.?

Code:
Sub macro1()
    With Range("A2:J5000")
        .Sort key1:=Range("A2"), Order1:=xlAscending, _
        Header:=xlGuess, Ordercustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
        .Columns(1).Find(what:="Non-ILEC", LookAt:=xlWhole).EntireRow.Insert
    End With
End Sub


Secondly,
I build this code, it works perfectly. But can someone please have a look and show me how it can be rewritten with less code lines. Basically, I want to insert 3 lines and type total and sum value.

Code:
Sub macro2()
    Dim FR&
    FR& = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
    Range("A" & FR& & ":A" & FR& + 2).EntireRow.Insert
    Range("A" & FR&).Offset(1).Value = "Total BS ILEC Orders"
End Sub


Have a great day,
Stan
 
Upvote 0
Third:

Code:
WorksheetFunction.Sum(Range("G1:G5000"))

Or:

Code:
Range("A5001").Formula = "=Sum(G1:G5000)"

Or:
Code:
With Range("A5001")
    .Formula = "=Sum(G1:G5000)"
    .value = .value '//hard values
End With
 
Upvote 0
irfananeeza,

And, the third part:

Before the macro:

Excel Workbook
A
11
22
33
44
55
66
77
88
99
1010
1111
1212
1313
1414
1515
1616
17Non-ILEC
1818
1919
2020
21
Sheet1



After the macro:

Excel Workbook
A
11
22
33
44
55
66
77
88
99
1010
1111
1212
1313
1414
1515
1616
17136
18Total BS ILEC Orders
19
20Non-ILEC
2118
2219
2320
24
Sheet1



Code:
Sub SUMinVBA()
    Dim FR&
    FR& = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
    Range("A" & FR& & ":A" & FR& + 2).EntireRow.Insert
    Range("A" & FR&).Offset(1).Value = "Total BS ILEC Orders"
    With Range("A" & FR&)
        .Formula = "=SUM(A1:A" & FR& - 1 & ")"
        .Font.Bold = True
    End With
End Sub


In the above code, change all "A"s to "G"s.


Have a great day,
Stan
 
Upvote 0
Stanleydgromjr/Alexander Barnes:

All I can say i am soooooo thankful to both of you.

It will take me a day or two to inform you if code runs okay, if I have completely understood its construction.

Thanks. I am very appreciative of it.
 
Upvote 0
Stanleydgromjr,

I understood everything very clearly in your code except one little thing.

I don't understand the use of this specific &" in the code. If I remove it, code does not function.

Rich (BB code):
.Formula = "=SUM(A1:A" & FR& - 1 & ")"


Interpretation of the code: Assume FR& = 6th row
Sum = (A1: A 5 ) To me it should finish here because the range is complete i.e. A1:A5

However, looking at your code
Sum=(A1: A5 & ") This red piece I have trouble understanding it.
 
Upvote 0
irfananeeza,
Code:
        'the next code line causes        ' Compile error:        '   Expected end of statement        '        ' the format is not correct        .Formula = "=SUM(A1:A" & FR& - 1 )"
Having a problem putting it into words.If I used:.Formula = "=SUM(A1:A5)"it would work.But, I did not know exactly what the range in the formula would be.I know from experience, that if you are using VBA to put a formula into a cell, you have to create what looks like a string.Sometimes you have to play to get the compiler to be satisfied.Sorry. I just can not explain it better.Have a great day,Stan
 
Upvote 0
irfananeeza,

Having a problem putting it into words.

Code:
        'the next code line causes
        ' Compile error:
        '   Expected end of statement
        '
        ' the format is not correct
        .Formula = "=SUM(A1:A" & FR& - 1 )"



If I used:
.Formula = "=SUM(A1:A5)"

it would work.


But, I did not know exactly what the range in the formula would be.

I know from experience, that if you are using VBA to put a formula into a cell, you have to create what looks like a string.

Sometimes you have to play to get the compiler to be satisfied.

Sorry. I just can not explain it better.


Have a great day,
Stan
 
Upvote 0
I'm not used to seeing the & in a variable name...Is that a convention of some kind?

Why not simply as below - now all "&" signs are string concatenation operators (is that easier to read?):
Code:
Sub SUMinVBA()
    Dim FR as Long
    FR = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)
    Range("A" & FR & ":A" & FR + 2).EntireRow.Insert
    Range("A" & FR).Offset(1).Value = "Total BS ILEC Orders"
    With Range("A" & FR)
        .Formula = "=SUM(A1:A" & FR - 1 & ")"
        .Font.Bold = True
    End With
End Sub
 
Upvote 0
I'm not used to seeing the & in a variable name...Is that a convention of some kind?

It's not part of the variable name, it's a type-declaration character

From the help file:

type-declaration character
A character appended to a variable name indicating the variable's data type. By default, variables are of type Variant unless a corresponding Deftype statement is present in the module.

& is for type Long
% is Integer
$ is String
@ is Currency
# is Double
! is Single

It's a VB6 article, but see here:
http://www.vb6.us/tutorials/using-variables-vb6
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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