It seems to have issues with the quotations on the Range("AF" * i-1) portion,
so I replaced it with the relative reference. It now gives me an application
or object defined error. Any ideas? Thanks again for your help!

Here's what it looks like now:


'create temp and lambda table and count # of instances data fell between
ranges
For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist

With Range("AG" & i)
.Formula = "=SumProduct((" & LambdaRange.Address & "
R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"
.Value = .Value
End With
Next




"Dave Peterson" wrote:

VBA doesn't have a Sumproduct function.

Untested...

Range("AG" & i).Value = activesheet.evaluate("SumProduct((" _
& LambdaRange.address & "" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value _
& "))")

Another option (which may be easier) might be to plop the formula into the cell,
then convert to values.

with Range("AG" & i)
.formula = "=SumProduct((" _
& LambdaRange.address & "" _
& Range("AF" & i - 1).Value & ") *" _
& (" & LambdaRange.address & "<" & Range("AF" & i).Value _
& "))"
.value = .value
end with

(also untested)

Matt S wrote:

Hello,

I am trying to create a table that counts the number of instances my
"lambda" value and my "temperature" value fall within a range. My lambda
values are in column P and my temp values in N. I am creating a sheet as
such, where I am trying to count the number of times lambda and temp fall
within the range. I.E. the first count would be the number of instances
lambda falls between 2 and 2.005.

Lambda Count Lambda Temp Count Temp
2 20
2.005 x 21 x
2.01 x 22 x
2.015 x 23 x

This is my code so far... please help! Please look to see why this is
giving me such a hard time. The error is coming in on the "LambaRange" and
"TempRange" within my SumProduct function. I tried replacing them with
P2:P1000, but then it highlighted the ":" and said ")" expected.

'Make Lambda and Temp Distribution Plots

Dim LambdaRange As Range
Dim TempRange As Range

Sheets("Runlog").Select

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define distribution table ranges
Ldist = 0.005
TDist = 1
NumLRows = (2 - 0.5) / Ddist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 2
Range("AG8").Value = 20

'Define Ranges within runlog
Set LambdaRange = Range(Cells(1, "P"), Cells(LastRow, "P"))
Set TempRange = Range(Cells(1, "N"), Cells(LastRow, "N"))


'create temp and lambda table and count # of instances data fell between
ranges
For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Value = SumProduct((LambdaRange _
Range("AF" & i - 1).Value) * _
(LambdaRange < Range("AF" & i).Value))
Next

For i = 9 To NumTRows
Range("AH" & i).Value = Range("AH" & i - 1).Value + TDist
Range("AI" & i).Value = SumProduct((TempRange = _
Range("AH" & i - 1).Value) * _
(TempRange <= Range("AH" & i).Value))
Next

--

Dave Peterson