Skywalker

07-11-2009, 09:56 AM

Hello,

I have put together the code below by taking snippets from other routines and seems works fine for what I got so far. But I need to develop further and possibly make it more efficient. Hoping someone can help me out.

What I want to do is quite simple but not sure how to do it:

1) Find first the occurance of 1 in column I.

2) Offset this cell by 10 rows down

3) Subtract the cell in the same row in column E from the cell in column E that corresponds to the row identified by stage 1 above. Place the result in activerow of column N

4) Repeat cycle - Find next occurance of 1 from the last cell

Below is what I have so far - basically the steps 1 to 3 for the first cycle. It finds the first ocuuring 1 value in column I (I17) and and then goes down by ten cells (I27) and then subtracts the value in column E from the value in column E that had the 1st occuring one in column E (E27-E17) and places this in N27.

Sub AAAAAA()

lastrow = Range("e14").End(xlDown).Row

Range("I3:I" & lastrow).Select

Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

True, SearchFormat:=False).Activate

x = ActiveCell.Offset(0, -4)

y = ActiveCell.Offset(10, -4)

ActiveCell.Offset(10, 5).Value = x - y

End Sub

But I'm not sure how I can repeat the cycle again - i.e from I27 find the next occuring 1 (which is actually I28!) , offset down by 10 cells and calculate the difference. I'm assuming I need some kind of loop function? If it possible to implement this without VBA then that would work for me as well.

Thanks,

Skywalker

I have put together the code below by taking snippets from other routines and seems works fine for what I got so far. But I need to develop further and possibly make it more efficient. Hoping someone can help me out.

What I want to do is quite simple but not sure how to do it:

1) Find first the occurance of 1 in column I.

2) Offset this cell by 10 rows down

3) Subtract the cell in the same row in column E from the cell in column E that corresponds to the row identified by stage 1 above. Place the result in activerow of column N

4) Repeat cycle - Find next occurance of 1 from the last cell

Below is what I have so far - basically the steps 1 to 3 for the first cycle. It finds the first ocuuring 1 value in column I (I17) and and then goes down by ten cells (I27) and then subtracts the value in column E from the value in column E that had the 1st occuring one in column E (E27-E17) and places this in N27.

Sub AAAAAA()

lastrow = Range("e14").End(xlDown).Row

Range("I3:I" & lastrow).Select

Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _

:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

True, SearchFormat:=False).Activate

x = ActiveCell.Offset(0, -4)

y = ActiveCell.Offset(10, -4)

ActiveCell.Offset(10, 5).Value = x - y

End Sub

But I'm not sure how I can repeat the cycle again - i.e from I27 find the next occuring 1 (which is actually I28!) , offset down by 10 cells and calculate the difference. I'm assuming I need some kind of loop function? If it possible to implement this without VBA then that would work for me as well.

Thanks,

Skywalker