Re: Sumproduct troubleshooting (new to me)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 here:
'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
.

Relevant Pages

  • Re: VB6 With/End With - how to pass the object itself?
    ... there would be a call for every item that is replaced by the use of With/End ... waste time creating the temp. ... dispatch table, use a look-up to find the 'class' reference, and then ... converts it to 'excode', and one of the things that happens in excode ...
    (microsoft.public.vb.general.discussion)
  • Re: A different design for closures
    ... in Jim I've a reference system similar to 'TclRef' I wrote for Tcl ... So Jim's lambda is implemented in Tcl itself: ... proc lambda { ... Salvatore Sanfilippo <antirez at invece dot org> We're programmers. ...
    (comp.lang.tcl)
  • Re: ACCESS97, try to relink reference to another MDB
    ... when you move your application to the second machine, the reference to ... Dim ref As Reference ... Set ref = Application.References ... Dim temp$, resp ...
    (comp.databases.ms-access)
  • Re: Safely Raising Events in Multithreaded app.
    ... The meaning of taking a temp reference is that if the last subscriber of the ... the TestEvent would be null and the TestEventcall ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SYS$QIOW - Performance Issue
    ... you would see a reference count of 2. ... $OPEN /READ/WRITE TEMP MBAnnn: ... stuck in the WRITE statement and you might need to kill the process. ... for the reading process to read from mailbox). ...
    (comp.os.vms)