[RESOLVED] VBA referencing cell ranges in different sheets

[tarheeljks]

syntax question this time, kind of a spinoff from my question about using a variable for countif

WorksheetFunction.Sum(Sheet21.Range("H1045:H1555"))

works

WorksheetFunction.Sum(Sheet21.Range(Cells(1045,8),Cells(1555,8)))

fails with a 400 error

but

Set vals = Range(Cells(firstrow, 11), Cells(rowindex - 1, 11))

this works. firstrow and rowindex are integer variables, vals is a range variable. by works i mean it sets the value fine, it isn't meant to do the same thing as the first two. noting this one b/c it lets me use cells(), whereas the other did not

so my question here is with regard to using the Cells() object to denote the cell references within a range. i assume the failed case has something to do with referencing a range from a different sheet. how can i correct this?

the reason i need to use cells() rather than "A1:B5" or something like that, is b/c i need a variable range, so i want toss a variable into Cells()

[MorDred]

Try declaring before the cells which sheet you want to look at. For instance:

WorksheetFunction.Sum(Sheet21.Range(Sheet21.cells(1045,8),Sheet21.Cells(1555,8)))

Amend your other code the same way.

HTH