| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct troubleshooting (new to me)
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct troubleshooting (new to me)
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Troubleshooting | Excel Discussion (Misc queries) | |||
| Need Help Troubleshooting | Excel Programming | |||
| SUMPRODUCT troubleshooting | Excel Worksheet Functions | |||
| troubleshooting | Setting up and Configuration of Excel | |||
| DDE Troubleshooting | Excel Programming | |||
Hybrid Mode
