VBA Sumif using variable defined ranges

[bggadish]

[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]Total Number of Periods[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This Period[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Budget Code[/TD]
[TD]Budget Description[/TD]
[TD]BAC[/TD]
[TD]Start Period[/TD]
[TD]End Period[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]Cumulatitive PV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Example[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Using the table I want to do a sumif function in VBA. If (B2) This Period = 3, sum periods 1, 2, and 3. The issue comes with the fact that the total number of periods changes and the current period changes so am using the code below (there is a bunch of other stuff, but this is the code that affects this portion). It selects the proper cell, The ranges are defined properly, but when i do the sumif function it doesn't work at all.

The Active cell is always the last period number. in this case it is the cell with 5

Set r = ActiveCell.Offset(1, 2)
Set SourceRange = Range("G3", Cells(3, LastCol))
SourceRange.Select
Set FillRange = Range("G4", Cells(4, LastCol))
FillRange.Select
r.Formula = "=SUMIF(Range(SourceRange),""<=""&""B2"",Range(FillRange)))"

Thanks in advance for the help

 

[Tyron]

Hey bggadish,

Good visual example, but I am a bit confused.

The way that your example is set up you have it so that there is only one budget that you would enter information for, but the way your question is formed it makes it sound like you would be keeping a list of multiple budgets on one sheet.

If multiple budgets you would have to start off with a column for both the Total and Current periods and try to define a maximum of total periods in order to make things a bit easier.

Could you clarify. This may help one of the other 16 people that viewed your post provide a workable answer that is beyond my VBA knowledge.

later

Ty