Hello
This solution is a bit of a fiddle. It might be more trouble then its worth…_
_.. Originally I was thinking of an Evaluate Range type solution, evaluating a spreadsheet formula. But I couldn’t figure out a working formula, so I gave up completely ..
But then later I saw Hans formulas answer, so I tried to use those.
But there is a small snag: His second formula, …._
In D2: =A2+IF(B2=B1,D1,0) , copied down
_... will work in the spreadsheet if its copied down, but/ because it relies on the formula having been done already in the previous row.
This gives problems with the Evaluate Range technique, because it will then only work “one row down”.
One workaround to this is to repeat the Evaluate range second formula code line for as many times as you may have repeated consecutive values in column B.
If you have an estimate for the likely number of repeated consecutive value in column B ,
and
if it small number
and
if the solution works at all for 30K of rows,
then
the solution is possibly worth a quick try
This is an example assuming that you would not have more than 3 consecutive
Ups or
Downs ( NoCnsUpDn = 3 )
Code: Select all
Sub RangeEvaluateHansFormulasEm3() ' http://www.eileenslounge.com/viewtopic.php?p=281028#p281028
Dim Em As Long: Let Em = Range("A" & Rows.Count).End(xlUp).Row
' ....... In D1: =A1
Let Range("=D1").Value = Evaluate("=A1")
' ....... In D2: =A2+IF(B2=B1,D1,0)
' Hans second formula needs that the last row is done already, so the Evaluate range will only get it right the first time, so it would need to be repeated for as many times as you might have consequtine same values in column b
Dim Cnt As Long, NoCnsUpDn As Long: Let NoCnsUpDn = 3
For Cnt = 1 To NoCnsUpDn Step 1
Let Range("=D2:D" & Em & "").Value = Evaluate("=A2:A" & Em & "+IF(B2:B" & Em & "=B1:B" & Em - 1 & ",D1:D" & Em - 1 & ",0)")
Next Cnt
' .....In C1: =IF(B1=B2,"",D1) Fill down from C2 to the end of the data.
Let Range("=C1:C" & Em & "").Value = Evaluate("=IF(B1:B" & Em & "=B2:B" & Em + 1 & ","""",D1:D" & Em & ")")
End Sub
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also