Take a look at .address in VBA's help.

You'll see that you can specify that, too:

RowAbsolute Optional Variant. True to return the row part of the reference as
an absolute reference. The default value is True.

ColumnAbsolute Optional Variant. True to return the column part of the
reference as an absolute reference. The default value is True.

LambdaRange.Address(rowabsolute:=false, columnabsolute:=false, )
referencestyle:=xlR1C1)

Or using them as positional parameters:

LambdaRange.Address(false, false, xlR1C1)
and you may see it as:
LambdaRange.Address(0, 0, xlR1C1)

(0 and false are interchangeable here.)

But I think you got your question backward. The default is true (or absolute
references).



Matt S wrote:

With the FormulaR1C1 function, the
LambdaRange.Address(referencestyle:=xlR1C1) gives a range that is not in the
form $P$8:$P$64008. It's in the form P8:P64008, so when I extend the formula
down, it is a floating range. Any way to lock it in place?

Thanks,
Matt

"Dave Peterson" wrote:

Glad you got it working!

Just for completeness...

.Formula = "=SumProduct((" & LambdaRange.Address _
& " R[-1]C[-1]) * (" & LambdaRange.Address & "< RC[-1])))"

is a mixture of R1C1 reference style (r[-1]c[-1] and rc[-1] and A1 reference
style (in both .address portions)--and you used .formula instead of
..formulaR1C1.

..FormulaR1C1 = "=SumProduct((" & LambdaRange.Address(referencestyle:=xlR1C1) _
& " R[-1]C[-1]) * (" _
& LambdaRange.Address(referencestyle:=xlR1C1) & "< RC[-1])))"

Still untested!

Matt S wrote:

Dave,

Thanks for your help! I played around with it and finally got it to work!
I'd actually like to keep the function in the worksheet, so I removed the
.value portion. Here's what I came up with below. Seems the difference
between what I did and what you did was that I did not put the .value portion
on the range("AF" & i). Not exactly sure why that would be the deciding
factor.

Thanks again!
Matt

'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) / Ldist
NumTRows = (1500 - 20) / TDist

'Initial lambda and temp values
Range("AF8").Value = 0.8
Range("AH8").Value = 20

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


'create temp and lambda table and count # of instances data fell between
ranges
'Resource for understanding SUMPRODUCT use is he
'http://xldynamic.com/source/xld.SUMPRODUCT.html#examples

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).Formula = "=SUMPRODUCT((" & LambdaRange.Address & "=
" & Range("AF" & i - 1) & ") * (" & LambdaRange.Address & "<=" & Range("AF" &
i) & "))"
Next

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

Not sure what was dif

--

Dave Peterson


--

Dave Peterson