[RESOLVED]newbie - help with syntax error

Trying to sum a range of cells on different sheet, where the upper and lower rows changes. I'm getting a the syntax error "run-time error '1004': application-defined or object-defined error" for the following line of code:


total_inven_lbs = Application.WorksheetFunction.Sum(Sheets("batchDetails").Range(Cells(newRow_batchDetails, 8), Cells(latestBatchrow_batchDetails, 8)))

Been a few hours already trying to research and solve.

Thanks.

What do you have

"newRow_batchDetails"
and
"latestBatchrow_batchDetails"

set to?

Many thanks for the reply.

I have them set to a cell containing a value:

newRow_batchDetails = Worksheets("batch details").Range("A2").Value + 1

latestbatchrow_batchDetails = Worksheets("batch details").Range("A10").Value

I have also attached a stripped down version of the workbook - i'm learning VB on the fly, so perhaps not the most elegant.

Your problem is more than likely within;
Range(Cells(newRow_batchDetails, 8)

Either use the Range or Cells, don't combine them. From what it looks like, you need to just use Range.

Try it like:

vb Code:

Dim rangestart as string
     Dim rangestop as string
 
     rangestart = "H" + newRow_batchDetails
     rangestop = "H" + latestBatchrow_batchDetails
 
     total_inven_lbs = Application.WorksheetFunction.Sum(Sheets("batchDetails").Range(rangestart, rangestop))

I tried the code (copied and pasted exactly as in spreadsheet) - but got a "run-time error '13': type mismatch"

Dim rangestart As String
Dim rangestop As String

rangestart = "H" + newRow_batchDetails
rangestop = "H" + latestbatchrow_batchDetails

On which line did you get the error?

Quote:

---

Originally Posted by newkarakara

I tried the code (copied and pasted exactly as in spreadsheet) - but got a "run-time error '13': type mismatch"

---

Did you paste my code in a sub by itself?? It was designed to work you your variables:
newRow_batchDetails
latestBatchrow_batchDetails

So in order for it to work it must be able to utilize those variables.
So you can add the variables in the beginning of my code and set them to what they are supposed to be, or you can change the lifecycle of the variable so that my code can access it, but you still need to set the variables before you can run my code...

At the bottom of this tutorial it has some more info about dealing with variables:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Many thanks again. I got the original code to work, not as elegant as your solution but "many to miles to go": seems the issue was including "value" at the end of the .cells reference, i.e. Cells(newRow_batchDetails, 8).value

total_inven_cost = Application.Sum(Worksheets("batch details").Range _
(Worksheets("batch details").Cells(newRow_batchDetails, 8), Worksheets("batch details").Cells _
(latestbatchrow_batchDetails, 8)))

Regarding the code you provided --yes, it was posted along with the definitions of the newRow_batchDetails, and
latestBatchrow_batchDetails variables.

your solution is correct, you must specify cells within the same sheet as the range

to make more elegant try like

vb Code:

with Worksheets("batch details")
  total_inven_cost = Application.Sum(.Range _
  (.Cells(newRow_batchDetails, 8), .Cells _
  (latestbatchrow_batchDetails, 8)))
end with

or set a worksheet object variable
it is always better to work with fully qualified ranges, even where not required

thanks again. i'll try this approach.