subscript out of range?

[n64play]

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?

[RobDog888]

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"?

[n64play]

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

[RobDog888]

Try by index number instead.

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.

[n64play]

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

its working now cheers!

[RobDog888]

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.