I'd try turning calculation to manual, then doing all those formulas. Then when
you're done with all of them, turn caculation back to automatic (or what you had
before).

Matt S wrote:

Dave,

Thank you so much for your help! One more issue, which may need its own
post... my files end up being larger than the row limit for excel, so these
lines of code take over 3 minutes to perform as it has to go through two
complete excel columns. Do you know a way I can make this calculation go a
little faster? Maybe I could do it for the first line then autofill it
downward? Here is the code that you've helped make work:

'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.5
Range("AJ8").Value = 20

'Copy lambda and temp values from the second sheet to the first
Sheets("Runlog2").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("P8:P" & LastRow & ", R8:R" & LastRow & ", N8:N" & LastRow).Copy

Sheets("Runlog1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("AN8").PasteSpecial Paste:=xlPasteValues

'Define Ranges within runlogs
Set FrontLambda = Range(Cells(8, "P"), Cells(LastRow, "P"))
Set BackLambda = Range(Cells(8, "R"), Cells(LastRow, "R"))
Set TempRange = Range(Cells(8, "N"), Cells(LastRow, "N"))

Set FrontLambda2 = Range(Cells(8, "AO"), Cells(LastRow, "AO"))
Set BackLambda2 = Range(Cells(8, "AP"), Cells(LastRow, "AP"))
Set TempRange2 = Range(Cells(8, "AN"), Cells(LastRow, "AN"))

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

'http://www.microsoft.com/office/community/en-us/wizard.mspx?query=sumproduct&w=1&type=question&dg= &cat=en-us-excel&lang=en&cr=US&pt=f3f7ac8a-4ea0-4c36-bed9-8feae6d75298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

For i = 9 To NumLRows
Range("AF" & i).Value = Range("AF" & i - 1).Value + Ldist
Range("AG" & i).FormulaR1C1 = "=SUMPRODUCT((" &
FrontLambda.Address(1, 1, xlR1C1) & _
"= R[-1]C[-1]) * (" & FrontLambda.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & FrontLambda2.Address(1, 1,
xlR1C1) & _
"= R[-1]C[-1]) * (" & FrontLambda2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"

Range("AH" & i).FormulaR1C1 = "=SUMPRODUCT((" & BackLambda.Address(1,
1, xlR1C1) & _
"= R[-1]C[-2]) * (" & BackLambda.Address(1, 1, xlR1C1) & _
"<= RC[-2])) + SUMPRODUCT((" & BackLambda2.Address(1, 1, xlR1C1)
& _
"= R[-1]C[-2]) * (" & BackLambda2.Address(1, 1, xlR1C1) & "<=
RC[-2]))"
Next

For i = 9 To NumTRows
Range("AJ" & i).Value = Range("AJ" & i - 1).Value + TDist
Range("AK" & i).FormulaR1C1 = "=SUMPRODUCT((" & TempRange.Address(1,
1, xlR1C1) & _
"= R[-1]C[-1]) * (" & TempRange.Address(1, 1, xlR1C1) & _
"<= RC[-1])) + SUMPRODUCT((" & TempRange2.Address(1, 1, xlR1C1)
& _
"= R[-1]C[-1]) * (" & TempRange2.Address(1, 1, xlR1C1) & "<=
RC[-1]))"
Next

--

Dave Peterson