VBA Cell Summation

[Grymjack]

I'm trying to create a UDF that sums a range of cells. I know there is
a function for this, but it doesn't work well with concatenated values.
Could someone tell me what is wrong with this code? When it gets to the
'With Worksheets(s_Book).Cells(rwIndex, colIndex)' line it bounces out
of the loop and throws a #VALUE error. Values that I've come into the
function with would be...

s_Book = "[Orders_June_2006.xls]Orders"
Begin_Column = 140
End_Column = 149
Begin_Row = 30
End_Row = 30

Function SUM_RANGE(s_Book As String, Begin_Column As Integer, End_Column
As Integer, Begin_Row As Integer, End_Row As Integer) As Integer

Rem *Application.Volatile

Rem * tracks total of the cells
Dim n_Total As Integer
n_Total = 0

Rem *cycling through the cell range
For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
With Worksheets(s_Book).Cells(rwIndex, colIndex)
n_Total = n_Total + .Value
End With
Next colIndex
Next rwIndex

End Function

 

[Bob Phillips]

You are passing a book and sheet to the function and trying to use that for
a worksheet object ... no can do.

Try this

Function SUM_RANGE(s_Book As String, _
s_Sheet As String, _
Begin_Column As Integer, _
End_Column As Integer, _
Begin_Row As Integer, _
End_Row As Integer) As Integer

Rem *Application.Volatile

Rem * tracks total of the cells
Dim n_Total As Integer
n_Total = 0

Rem *cycling through the cell range
For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
With Workbooks(s_Book).Worksheets(s_Sheet).Cells(rwIndex,
colIndex)
n_Total = n_Total + .Value
End With
Next colIndex
Next rwIndex

End Function

and call like

=SUM_ RANGE("Orders_June_2006.xls","Orders",140,149,30,30)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Grymjack]

Thanks for taking the time to answer my question. Unfortunately that
didn't fix the issue. It still bounces out at the "With
Workbooks(s_Book).Worksheets(s_Sheet).Cells(rwIndex, colIndex)' line
with a #VALUE error

 

[Abanana]

My spreadsheet calculation is set to i.e

10 + 10 = 100

or my percentage is also set instead of 100% I get 1000%

can you help please

Many thanks

 

[Bob Phillips]

Apart from failing to return the value (legacy of your code), it works fine
for me. Here is a version, without wrap-around, returning the value

Function SUM_RANGE(s_Book As String, _
s_Sheet As String, _
Begin_Column As Integer, _
End_Column As Integer, _
Begin_Row As Integer, _
End_Row As Integer) As Integer

Rem *Application.Volatile

Rem * tracks total of the cells
Dim n_Total As Integer
n_Total = 0

Rem *cycling through the cell range
For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
With Workbooks(s_Book).Worksheets(s_Sheet) _
.Cells(rwIndex, colIndex)
n_Total = n_Total + .Value
End With
Next colIndex
Next rwIndex
SUM_RANGE = n_Total
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Grymjack]

Hmmmmm, thanks. This time it worked. Dunno why it gave me grief on the
last test!?!