How to use excel sum function using vba

[irfananeeza]

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.

 

[mole999]

and I always used "&" for anding things,

this & that & the other

 

[Scott Huish]

It's for concatenation too, but is used differently
I don't use the type declaration characters.

The article I posted above states this:

Use of type-declaration characters in VB is not encouraged; the modern style is to use the "As" clause in a data declaration statement

 

[mole999]

I have so much to learn )

 

[irfananeeza]

Stanleydgromjr, thanks for your explanation. I,certainly, appreciate it.

The other question I was going to ask you about was "&" character. Why did you use the & character in your Dim statement. But HOTPEPPER clarified it in response to the same question posed by Alexander. So, I am clear about it.

Alexander/Stanleydgromjr/Hotpepper:
In my little knowledge the declared variable should be "Range". However, I noticed both of you used the variable type as "Long".

In this e.g. we are trying to find "Non-ILEC" which is a Range. So, shouldn't my variable be "Range" and not Long.? Or it doesn't make any difference.?

 

[Scott Huish]

In this e.g. we are trying to find "Non-ILEC" which is a Range. So, shouldn't my variable be "Range" and not Long.? Or it doesn't make any difference.?

If Non-Ilec is a Named Range then the codes presented are incorrect. But it appears that Non-ILEC is text not a range. If the VBA Find was used, this would return a Range (the range (cell) holding the text Non-ILEC) and then should be dimmed as Range. This is the method I would use.
However the worksheet function MATCH was used, this returns an integer number, therefore type Long for the Integer. However if it doesn't find it, it returns an error. There is no test in the codes above for if the text Non-ILEC isn't found.

It's really easy to test for if you use Find.

Sub SUMinVBA()
Dim FR As Range
Set FR = Range("A:A").Find("Non-ILEC", LookAt:=xlWhole)
If FR Is Nothing Then
    MsgBox "Non-ILEC NOT FOUND"
    Exit Sub
End If
FR.Resize(3).EntireRow.Insert
FR.Offset(-2) = "Total BS ILEC Orders"
With FR.Offset(-3)
    .Formula = "=SUM(A1:A" & FR.Row - 4 & ")"
    .Font.Bold = True
End With
End Sub

 

[xenou]

I believe following stanleydgromjr I'd picked up on the line:

FR = Application.WorksheetFunction.Match("Non-ILEC", Range("A:A"), 0)

The above formula returns a numeric value, which is the row number, so we dimmed the variable as a long to hold row numbers (you'd use the row number to identify the cell as needed in your code).

That's about it - Hotpepper has already provided an excellent answer to show how to use a range reference variable instead - with the added benefit of an easy test to see if it was found. The code with Match() would I think error if no match was found.

Sounds like we've covered a lot of good ground with you this week,
Regards, Alex

 

[irfananeeza]

Alexander /HOTPEPPER/ Stanleydgromjr/Jindon / Peter_ss and many other:

Thanks to all of you.

Hotpepper you explained very well when would I use "range" vs. "long" and it's been understood very well as well.

Infact, the joy of learning new stuff puts a smile on my face. The fact that my teachers are VBA subject matter experts, and I am being couched by professionals from all over the world makes me feel very good.

I appreciate time, energy and effort you guys share with people like myself and others who cherish this sharing.

Thanks guys.... I luv ya.