VBA Cell Summation

G

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
 
G

Guest

=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
 
J

Joost Lenaerts

Alternatively, you could modify your code to include a separate
workbook parameter:
=-SUM_RANGE("Orders_June_2006.xls","Orders",140,149,30,30)

And in your VBA:
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

Dim n_Total As Integer
n_Total = 0

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

Gary''s Student schreef:
=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
--
Gary''s Student


Grymjack said:
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
Click to expand...
Click to expand...
 
G

Grymjack

Thanks for taking the time to answer my question. I tried your
suggestion. Unfortunately it still dumps out at the 'With' line
throwing a #VALUE error. The Workbook is open, the Sheet is properly named.


Joost said:
Alternatively, you could modify your code to include a separate
workbook parameter:
=-SUM_RANGE("Orders_June_2006.xls","Orders",140,149,30,30)

And in your VBA:
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

Dim n_Total As Integer
n_Total = 0

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

Gary''s Student schreef:
=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
Click to expand...
Click to expand...
 
G

Grymjack

Thanks Gary for taking the time. Would you have a code snippet example
that you could post as an example?





=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
-- Gary''s Student "Grymjack" wrote:


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
Click to expand...
Click to expand...
 
G

Guest

Perhaps:

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
Dim sBook, sSheet As String
Dim n_Total As Integer

sBook = Mid(s_Book, 2, Application.Find("]", s_Book, 1) - 2)
sSheet = Right(s_Book, Len(s_Book) - Application.Find("]", s_Book, 1))
n_Total = 0

For rwIndex = Begin_Row To End_Row
For colIndex = Begin_Column To End_Column
n_Total = n_Total +
Workbooks(sBook).Sheets(sSheet).Cells(rwIndex, colIndex).Value
Next colIndex
Next rwIndex
SUM_RANGE = n_Total
End Function


works for =-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
--
Gary's Student


Grymjack said:
Thanks Gary for taking the time. Would you have a code snippet example
that you could post as an example?





=-SUM_RANGE("[Orders_June_2006.xls]Orders",140,149,30,30)
does not work
=-SUM_RANGE("Orders",140,149,30,30)
works just fine

you may need to parse the string variable, ACTVATE the workbook, and then
use only the worksheet in your WITH statement.
-- Gary''s Student "Grymjack" wrote:


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

range problem 2
error message on populating combobox 10
VBA cell values 1
Help with SUMIF in VBA 1
auto sum macro if data is present above the cell 2
Comparing two books and two sheets, Problems with color coding correct sheet 1
cell value under VBA 1
range object error 10

Top