VBA cell values

[Grymjack]

OK, I seem to be mis-coding this. Can anyone tell me what is wrong with
this VBA code?.....or a better way to do it?

*******
This line is supposed to return a value in a specific cell based on
row/column position. The correct values seem to be getting fed into
there, but it throws a #VALUE error? Is there a better way to retrieve a
specific cell value somewhere on a worksheet?
*******

n_Total = Workbooks(s_Book).Worksheets(s_Cases_Sheet).Cells(n_Row, n_Column)

####
Sample values for above
s_Book = "Inventory_Entry_June_2006.xls"
s_Cases_Sheet = "Cases"
n_Row = 4
n_Column = 10
####


*******
This line is supposed to return a sum in a range of cells. Again the
right values appear to be getting fed into the function?
*******

n_Total = Application.WorksheetFunction.Sum(Range(s_Start_Book + s_Sheet
+ s_Start_Cell + ":" + s_End_Cell))

####
Sample values for above
s_Start_Book = "[Orders_June_2006.xls]"
s_Sheet = "Orders!"
s_Start_Cell = "O_6697_19"
s_End_Cell = "O_6697_29"
^^^ These are names cell ranges, and are properly named
####

 

[JMB]

This syntax seems to work fine for me. Is there a #VALUE error in the cell
you are referencing?
n_Total = Workbooks(s_Book).Worksheets(s_Cases_Sheet).Cells(n_Row, n_Column)


This is not right
n_Total = Application.WorksheetFunction.Sum(Range(s_Start_Book + s_Sheet
+ s_Start_Cell + ":" + s_End_Cell))

Although you are using the Worksheet SUM function, you should use VBA's
syntax for referring to your range:
With Workbooks(s_Start_Book).Worksheets(s_Sheet)
n_Total = Application.WorksheetFunction.Sum(.Range( .Range
(s_Start_Cell), .Range(s_End_Cell)))
End With

You'll need to lose the [ ] in the workbook variable and the ! in the
worksheet variable. The only time you need to build a string similar to the
manner you are using is if you literally wanted =SUM(Cell1:Cell2) to appear
as a formula in a cell

Range("A11").Formula = "=SUM(" &
Range(A1:A10).Address(False,False,xlA1,False) & ")"

would put =SUM(A1:A10) in cell A11. The fourth argument of Address, if set
to TRUE, will return the fully qualified external range address - so you
don't have to worry about the brackets, !, and single apostrophe's in
external range references.