Selecting a Range using .Cells in VBA

[purplefox]

Hi MrExcel Community,

I have a query about how to write sometyhing in VBE,

As part of a larger Macro I need to be able to sum a range, although the columns and row references of this range are at the outset unknown.

How I imagine doing this is....

WorksheetFunction.sum (Sheet3.Range(sheet3.Cells(a,b) & ":" & Sheet3.Cells(a,c)))

I have tried this on it's own as a Macro replacing the a, b & c for values for specific cells, and prefixing with "msgbox" so that it should return the sum in a message box but instead I just get Errors.
I have played around trying to use Columns() & rows() but this doesn't work.
If I replace the Range with .Cells for Sheet3.Range("A1:A2") (not the range I'm looking for, just an example) it works fine and sums, but while I know how to define a range such as Range("A" & i & ":B" & i) I know I have substituted the reference for Cells, but I can't remember if I have ever gotten it to work in the past

Advice Help is appreciated,

Thanks

Andy

 

[VoG]

Try

MsgBox WorksheetFunction.Sum(Sheet3.Range(Sheet3.Cells(a, b), Sheet3.Cells(a, c)))

 

[purplefox]

Thanks Peter, You're a star, it worked like a charm.

I knew I had done it before but couldn't for the life of me remember what I needed to do.

Thanks

Andy