fredlo2010
07-05-2012, 03:05 PM
Hello guys,
I am trying to define a range based on a column that has consecutive numbers.
My Column "H" has this data:
1
2
3
1
2
3
4
5
1
2
1
2
3
4
5
6
7
I want to copy a formula on column "J" in an alternating way. For my first block of consecutive numbers I want to add to the row in column "J" (rows values have been omitted) G+H and for the next block then G+H/H so on alternating each block with this two formulas.
Any ideas?
I was thinking to check the current cell with the value of the one on top.
Here is the code I managed to put together. :(
Dim i As Long
Dim ii As Long ' this will be the count of consecutive values but I cannot find a way to define it
lastrow = Range("H" & Rows.Count).End(xlUp).Row
With Range("H2:H" & lastrow)
For i = 1 To lastrow
If .Cells(i, 8) = .Cells(i - 1, 8) Then
Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]"
Else
.Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]/RC[-2]"
Next i
I am trying to define a range based on a column that has consecutive numbers.
My Column "H" has this data:
1
2
3
1
2
3
4
5
1
2
1
2
3
4
5
6
7
I want to copy a formula on column "J" in an alternating way. For my first block of consecutive numbers I want to add to the row in column "J" (rows values have been omitted) G+H and for the next block then G+H/H so on alternating each block with this two formulas.
Any ideas?
I was thinking to check the current cell with the value of the one on top.
Here is the code I managed to put together. :(
Dim i As Long
Dim ii As Long ' this will be the count of consecutive values but I cannot find a way to define it
lastrow = Range("H" & Rows.Count).End(xlUp).Row
With Range("H2:H" & lastrow)
For i = 1 To lastrow
If .Cells(i, 8) = .Cells(i - 1, 8) Then
Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]"
Else
.Cells(i, 10).Resize(i + ii, 10).FormulaR1C1 = "=RC[-3]+RC[-2]/RC[-2]"
Next i