VBA Cell Summation

  • Thread starter Thread starter Grymjack
  • Start date Start date
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
 
=-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.
 
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...
 
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...
 
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...
 
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
how to get rid of "^M" in the output file 3
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
Excel VBA -- Finding last row in group of columns 7

Back
Top