[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.
Re: newbie - help with syntax error
What do you have
"newRow_batchDetails"
and
"latestBatchrow_batchDetails"
set to?
1 Attachment(s)
Re: newbie - help with syntax error
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.
Re: newbie - help with syntax error
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.
Re: newbie - help with syntax error
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))
Re: newbie - help with syntax error
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
Re: newbie - help with syntax error
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
Re: newbie - help with syntax error
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.
Re: newbie - help with syntax error
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
Re: newbie - help with syntax error
thanks again. i'll try this approach.