Autoselect relevant range for Sumproduct function

[Hamond]

Hello

I have the following sumproduct formula in row M2 in a sheet

=-SUMPRODUCT((L11:L372)*(M11:M372))

Cells in the specified range above in in Column L have a formula that returns either a 0, 1 or -1.

I want the start range for the formula to begin on the first occurance of a 1 in column L and the end range to end at the last occurance of -1 in column L. Is it possible to get the sumproduct to autoselect the range based on this criteria?

For example, if the first 1 only occurs until L15, and the last -1 occurs in L360, then I only want to run the SUMPRODUCT formula on the range ((L15:L360)*(M15:M360)).

A couple more examples should make it clear

First 1 occurance: L20
Last -1 occurance L300
Range: Pick up only cells L20:L300 & M20:M300 in formula

First 1 occurance: L91
Last -1 occurance L358
Range: Pick up only cells L91:L358 & M91:M358 in formula

Thanks,

Hamond

[GTO]

Hi Hammond,

Not sure how many places you are putting this/or similar formula updating in, so a simple example.

This should update the formula in cell A1. If just in one cell, maybe use the Worksheet Change event amd limit to changes in L Col.

[vba]
Sub FindOnes()
Dim FirstRange As Range
Dim LastRange As Range

Set FirstRange = Range("L:L").Find(1, Cells(Rows.Count, "L"), xlValues, _
xlWhole, xlByColumns, xlNext)
Set LastRange = Range("L:L").Find(-1, Cells(1, "L"), xlValues, xlWhole, _
xlByColumns, xlPrevious)

If FirstRange Is Nothing _
Or LastRange Is Nothing _
Then Exit Sub

Range("A1").Formula = "=-SUMPRODUCT((L" & FirstRange.Row & ":L" & LastRange.Row & ")" & _
"*(M" & FirstRange.Row & ":M" & LastRange.Row & "))"
End Sub
[/vba]

Hope that helps,

Mark

[Hamond]

Hi Mark,

Thanks for the code. The Sumproduct formula is only in one cell.

So your saying I can get the value to automatically update by running the macro whenever there is a change in Column L via the Worksheet Change event?

I'm assuming the values would update very quickly?

[Hoopsah]

Hi

I had a similar problem last week and it is explained in this thread:

http://www.vbaexpress.com/forum/showthread.php?t=27280

Hope this helps

Hoopsah

[mdmackillop]

A non macro solution, using a few helper cells

[Hamond]

Excellent Gentleman,

I think this gives me everything I need. I commend you of great a job!

Hamond