Sum cell range on specified worksheet

[sricketts]

I have the following line of VBA code that sums a range of cells on the active worksheet. I need to change that to sum on a specified worksheet. Is there a way to do that? I thought it would be simple, but have had no luck... Actually using variables for the cells, but simplified here for example's sake.

If Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(123, 2))) = 0

 

[Leeinsa]

Try Using:

If Sheets("your worksheet name").Range("A2,B3,F10....etc")

changing the red text to your own sheet and range.

Hope it helps,

Lee.

 

[Travis]

I have the following line of VBA code that sums a range of cells on the active worksheet. I need to change that to sum on a specified worksheet. Is there a way to do that? I thought it would be simple, but have had no luck... Actually using variables for the cells, but simplified here for example's sake.

If Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(123, 2))) = 0

Application.WorksheetFunction.Sum(Sheets("Sheet1").Range(Cells(2, 2), Cells(123, 2)))

 

[sricketts]

Thanks for the suggestion. I coded it this way then:

If Sheets("data").Range(Cells(2, 2), Cells(123, 2)) = 0 Then ...


which returned a run-time error 1004, "application-defined or object-defined error".

 

[hiker95]

sricketts,

Try:

If Application.WorksheetFunction.Sum(Worksheets("Sheet1").Range(Cells(2, 2), Cells(123, 2))) = 0 Then

 

[sricketts]

Thanks Travis. Tried this:

If Application.WorksheetFunction.Sum(Sheets("data").Range(Cells(2, 2), Cells(123, 2))) = 0 Then


...and got the same mentioned runtime error.

 

[sricketts]

Hiker ~ thanks...changed Sheets to Worksheets and got the same runtime error.

 

[swat2]

Go to the immediate window and try
? Application.WorksheetFunction.Sum(Worksheets("data").Range(Cells(2, 2), Cells(123, 2)))

all on one line. This will at least tell you if you can get an answer from this sum.

 

[wigi]

If WorksheetFunction.Sum(Range(Sheets("data").Cells(2, 2), Sheets("data").Cells(123, 2))) = 0 Then

 

[pgc01]

Hi

Remark:

In a range statement

Range(Cells(),Cells())

all the range objects must be in the same worksheet or else you get an error

These are wrong:

Worksheets("Data").Range(Cells(),Cells())
Range(Worksheets("Data").Cells(),Cells())
Worksheets("Data").Range(Worksheets("Data").Cells(),Cells())
etc

This is right:

Result = WorksheetFunction.Sum(Worksheets("Data").Range(Worksheets("Data").Cells(2, 2), Worksheets("Data").Cells(123, 2)))

Or, easier to write and to read:

With Worksheets("Data")
    Resut = WorksheetFunction.Sum(.Range(.Cells(2, 2), .Cells(123, 2)))
End With

HTH