Excel VBA help

[jersiq]

I have a UDF ConsecutiveSum that takes a range of cells as an argument.

I am trying to write a macro that will use this within a loop and with a dynamic cell range size.

The problem I am having (very possible it's syntax related) is passing the varible cell range to the function:

Dim timeRange as range //variable amount of cells in a row

For i = 3 to Last_Row_sheet1 -3
Set timeRange = Range(Cells(i,2), Cells(i,Last_Col_Sheet1 -1))
Sheets("Pivot").Cells(i, Last_Col_Sheet1).offset(0,4).Formula = "=ConsecutiveSum //stuck here

I am unsure of how to exactly input this. I have tried "=ConsecutiveSum("&timeRange")"
and other variations, but I think I am missing something here.

I have also tried using the Application.WorksheetFunction method, but from what I understand, this is only for certain pre-defined Excel functions, and not UDF's

So how could I pass my cell range to a UDF in this fashion?

 

[mayest]

Since timerange is a range variable, have you tried using its address property? Something like this:

Sheets("Pivot").Cells(i, Last_Col_Sheet1).offset(0,4).Formula = "=ConsecutiveSum(" + timerange.address +")"

I think that should result in a cell formula like =ConsecutiveSum(A1:B5), though the exact range will be different of course.

EDIT: Also make sure that you are using a Sub not a Function to place the formula in the cell.

 

[jersiq]

Works like a charm. Thanks a bunch.