subscript out of range?

I've got a piece of code that adds the values in cells J12:J30 together and gives the total. In theory this should work:

Set Comm = Worksheets("Sheet2").Range("J12:J50")
showComm = Application.WorksheetFunction.Sum(Comm)
MsgBox showComm

I keep getting a "Subscript out of range" error. Any ideas?

I tried your code with and with data in the range and I got no errors, only a sum with data and a 0 for no data.
Do you have a sheet named "Sheet2"?

Hi RobbDogg,
Yeah, Ive got a sheet called Sheet2 :) on the IDE it shows as Sheet2(Pipeline), and out of interest i tried using 'Pipeline' instead of 'Sheet2' but it didnt make a difference. I cant understand it myself, it SHOULD work :(

Try by index number instead.

VB Code:

Set Comm = Worksheets(1).Range("J12:J50")
'Or
Debug.Print Worksheets.Count

The count will tell you how many indices there are in the workbook. Then use the .Name property to
see which sheet it is referring to.

changed it to
Set Comm = Worksheets(2).Range("J12:J50")

its working now :D cheers!

:thumb: If you Debug.Print Worksheets(2).Name and you get a pipe char at the end, you still may be able to ref the sheet
by its name if you try adding a $ sign after "Sheet2$" in code. The pipe may represent something special of the sheet. Or try renaming
the sheet to something else as a test.