Function Help

[cstimart]

I'm stumped, it's too early in the morning...

How do I convert this cell calculation =SUM(R7C4:R11C4) into a function? I want to use the R1C1 version, as the row and columns will be variables in the end.

Function Test()
Dim num as integer
   num = WorksheetFunction.Sum(Sheets("Sheet1").????????) 
End Function

 

[Jonmo1]

Try

Function Test()
Dim num as integer, BeginRow As Long, EndRow As Long, BeginCol As Long, EndCol As Long
BeginRow = 7
EndRow = 11
BeginCol = 4
EndCol = 4
 
With Sheets("Sheet1")
    num = Application.Sum(.Range(.Cells(BeginRow, BeginCol), .Cells(EndRow, EndCol)))
End With
End Function

Hope that helps.

 

[Sektor]

As argument for Evaluate is string, you can construct any formula by concatenation.

Sub Eval()
    MsgBox Evaluate("=SUM(R7C4:R11C4)")
End Sub