Read in/Write out Excel data in VBA (Excel 2003)

[albie317]

I am extremely new to using VBA and am working on some sample codes. I am only trying to read data into VBA from my Excel sheet (name the range) and then sum the columns within VBA using a loop and then write the answers back out to the Excel sheet, but I want to do all the calculations within VBA. I've attached what I've been trying to do. Thanks!

[HansV]

Welcome to Woody's Lounge!

You could do this using formulas, of course, and it is also possible to use Excel worksheet functions in VBA. Here is an example using Sum in VBA:

Sub SumCol()
Dim rng As Range
Dim ColCnt As Integer
Dim RowCnt As Integer

Set rng = Range("A1:H9")

For ColCnt = 1 To rng.Columns.Count
rng.Cells(rng.Rows.Count + 1, ColCnt) = _
Application.WorksheetFunction.Sum(rng.Columns(ColC nt))
Next ColCnt
End Sub

and here is an example that performs the calculations in VBA:

Sub SumCol()
Dim rng As Range
Dim ColCnt As Integer
Dim RowCnt As Integer
Dim dblSum As Double

Set rng = Range("A1:H9")

For ColCnt = 1 To rng.Columns.Count
dblSum = 0
For RowCnt = 1 To rng.Rows.Count
dblSum = dblSum + rng.Cells(RowCnt, ColCnt)
Next RowCnt
rng.Cells(rng.Rows.Count + 1, ColCnt) = dblSum
Next ColCnt
End Sub

[albie317]

Thanks Hans!

I think I'll be visiting this site frequently! I'm just getting use to the VBA language, so I may have more questions.

Thanks again, that worked!

Melissa

[AlanMiller]

Welcome to the Lounge Albie!

With regard to your code, there are a couple of things I can spot:

If you want your procedure visible under Tools > Macro > Macros (Alt+F8) it will need to be a Sub, as opposed to a Function.

You need to terminate each For loop with a Next statement:
<pre>For ColCnt = 1 To 8
SumCol(ColCnt, 1) = 0
For RowCnt = 1 To 9
SumCol(ColCnt, 1) = SumCol(ColCnt, 1) + Ary(RowCnt, ColCnt)
Next RowCnt
Next ColCnt
</pre>


"Ary" is an undefined function in the above. I suspect you want Cells(RowCnt, ColCnt).Value here.
SumCol is meant to be a cumulative total, yes? If so it should be declared as:
Dim SumCol As Long 'if that's the type you want

and should not carry subscripts.

Very often, in XL VBA, it's far more efficient to use worksheet functions than loops. Take a look at Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops for some good examples. Using VBA (without the loops), your code would simply be:

SumCol = Application.WorksheetFunction.Sum (Range("A1:H9"))

Hope that gets you on your way.

Alan

[albie317]

Thanks for the welcome Alan!

I really appreciate everyone's responses. I believe this will be a great site for me to help gain more knowledge of VBA.

It does seem that using the worksheet functions makes more sense.

I'll take your suggestions and see what I can do with my small code. [img]/forums/images/smilies/smile.gif[/img]