Howto repeat/cycle code through all populated cells in column

[Skywalker]

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

[Krishna Kumar]

Hi Skywalker,

Welcome to board!!

Try

Sub kTest()
Dim Rng As Range, r As Range, fAdd As String

Const Find1 As Long = 1
Const rO As Long = 10
Const cO As Long = -4

Set Rng = Range("i3:i" & Range("e" & Rows.Count).End(xlUp).Row)

With Rng
Set r = .Find(Find1, LookIn:=xlValues)
If Not r Is Nothing Then
fAdd = r.Address
Do
r.Offset(rO, 5) = r.Offset(rO, cO) - r.Offset(, cO)
Set r = .FindNext(r)
Loop While Not r Is Nothing And r.Address <> fAdd
End If
End With
End Sub
HTH

[GTO]

Greetings Skywalker,

I'd certainly llike to echo Krishna in welcoming you here :-)

Krishna's certainly looks tidier, but I thought to go ahead and post what I had typed up, in case the commenting is of any help.

Option Explicit

Sub exa()
Dim wksCurrent As Worksheet
Dim rngRangeToSearch As Range
Dim rngValFoundCell As Range
Dim lngLastRow As Long
Dim strFirstAddress As String

'// Set a reference to the sheet that contains the range of cells that we're //
'// interested in. //
Set wksCurrent = ThisWorkbook.Worksheets("Sheet1")

'// I tried to replicate your method; would note I usually look from the bottom up.//
lngLastRow = wksCurrent.Cells(14, "E").End(xlDown).Row

'// The range to be searched. //
Set rngRangeToSearch = wksCurrent.Range("I3:I" & lngLastRow)

With rngRangeToSearch
'// You do not need the quotes if finding a numerical value; the recorder //
'// just puts them there by default. I was not sure whether you have the //
'// 1's manually entered (presumed) or as a result of a formula - so used //
'// xlValues. //
Set rngValFoundCell = .Find(What:=1, _
After:=wksCurrent.Range("I" & lngLastRow), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

'// In case we don't find a 1, allow an exit. //
If rngValFoundCell Is Nothing Then Exit Sub

'// Assign the address of the first cell found to use in preventing an //
'// endless loop in searching. //
strFirstAddress = rngValFoundCell.Address

'// As we know we found at least one matching cell value, we can DO at least//
'// once, and test whether to repeat at the bottom of the loop. //
Do
'// We can skip any activating or selecting by offsetting based on the //
'// cell with the val found. //
rngValFoundCell.Offset(10, 5).Value = _
rngValFoundCell.Offset(, -4).Value - rngValFoundCell.Offset(10, -4).Value

'// Set a reference to the next cell w/1. //
Set rngValFoundCell = .FindNext(rngValFoundCell)

Loop While Not rngValFoundCell Is Nothing _
And Not rngValFoundCell.Address = strFirstAddress
End With
End Sub


A great day to all,

Mark

[Skywalker]

Hi Krishna/GTO,

Thanks very much for the code. I tested it out and outputs from both codes are the same. However, I fear I may have not explained myself properly.

The calulation in column N for the first occurance of 1 is fine. But then the for the next cycle I'm not expecting to see another cell in column N populated at least for a minumum of 10 rows.

I think currently the macro is calculating based on every instance of the occcurance of 1 from the first occurance of 1 onwards. But I only want to look for the next occurance of 1 relative to the last populated cell in column N.

So for stage 4) Repeat cycle - Find next occurance of 1 from the last cell - by this is mean the last populated cell in column N. So if first occurance of 1 is in I17. then the fisrt calculation result is in N27. I want to find the next occuring 1 FROM N27 onwards and repeat the cycle. Currently it's calculating based on any occuring 1 rather than relative to when the last cell in column N is populated with the calculation. Hope this is clearer.

Sorry for any confusion and or wasted effort.

Thanks,

SW

[GTO]

Hi Krishna/GTO,

...I tested it out and outputs from both codes are the same....

...So for stage 4) Repeat cycle - Find next occurance of 1 from the last cell - by this is mean the last populated cell in column N. So if first occurance of 1 is in I17. then the fisrt calculation result is in N27. I want to find the next occuring 1 FROM N27 onwards and repeat the cycle. Currently it's calculating based on any occuring 1 rather than relative to when the last cell in column N is populated with the calculation. Hope this is clearer...

Hi SW,

Not a problem. I think but not utterly sure we're on the same page now, so definitely in a throwaway copy of your wb, try:

In a Standard Module:

Option Explicit

Sub exa2()

Dim _
wksCurrent As Worksheet, _
rngRangeToSearch As Range, _
rngValFoundCell As Range, _
lngLastRow As Long, _
lngLastFoundRow As Long, _
strFirstAddress As String

Set wksCurrent = ThisWorkbook.Worksheets("Sheet1")
'// Now looks from the bottom up//
lngLastRow = wksCurrent.Cells(Rows.Count, "E").End(xlUp).Row
Set rngRangeToSearch = wksCurrent.Range("I3:I" & lngLastRow)

With rngRangeToSearch

Set rngValFoundCell = .Find(What:=1, _
After:=wksCurrent.Range("I" & lngLastRow), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)

If rngValFoundCell Is Nothing Then Exit Sub

strFirstAddress = rngValFoundCell.Address
lngLastFoundRow = rngValFoundCell.Row

Do
rngValFoundCell.Offset(10, 5).Value = _
rngValFoundCell.Offset(, -4).Value - rngValFoundCell.Offset(10, -4).Value

Set rngValFoundCell = .FindNext(rngValFoundCell)

Do While Not rngValFoundCell Is Nothing _
And Not rngValFoundCell.Address = strFirstAddress _
And Not rngValFoundCell.Row >= 10 + lngLastFoundRow

Set rngValFoundCell = .FindNext(rngValFoundCell)
Loop

lngLastFoundRow = rngValFoundCell.Row

Loop While Not rngValFoundCell Is Nothing _
And Not rngValFoundCell.Address = strFirstAddress
End With
End Sub


As to the calculated results, I came up w/different results. I think I got it correctly calculating, but as mentioned, a bit tired, so maybe backwards.

Hope that helps,

Mark

[Krishna Kumar]

Hi,

In my code replace the line

Set r = .FindNext(r)

with

Set r = .FindNext(r.Offset(rO))

HTH

[Skywalker]

Gents, thanks again for the code.

GTO - I tried running your code but nothing seems to happen.

Krishina, changing that line makes the code work exactly how I need it so thanks. Just one thing though, at the end of the macro it generates an error message:

"Unable to get the findnext property of the range class". Debugging takes you to the line: Set r = .FindNext(r.Offset(rO))

I'm pretty sure this is happening because the macro as got to the end of the series and there's no more data in columns I and E. Is there way to tell it to only keep calculating the value in column N as long as there is data in column E (or I)?

Thanks,

SW

[GTO]

Gents, thanks again for the code.

GTO - I tried running your code but nothing seems to happen.

Krishina, ...at the end of the macro it generates an error ..."Unable to get the findnext property of the range class". Debugging takes you to the line: Set r = .FindNext(r.Offset(rO))

Hi again SW,

I think you're only a couple of posts away from being able to post an example workbook. Til then, take a look at this.

I do not see how nothing happens. I did see that Krishna and I took what is supposed to be getting subtracted from what differently and made mention last post.

I also see the errror you refer to. Though I chose a nested Do...Loop to get around this, I would think that simply finding the last row in Col I and adding 10 (or maybe 11) would take care of it. (I did not test that though)

By chance, is this homework?

Mark

[Krishna Kumar]

Gents, thanks again for the code.

Krishina, changing that line makes the code work exactly how I need it so thanks. Just one thing though, at the end of the macro it generates an error message:

"Unable to get the findnext property of the range class". Debugging takes you to the line: Set r = .FindNext(r.Offset(rO))

I'm pretty sure this is happening because the macro as got to the end of the series and there's no more data in columns I and E. Is there way to tell it to only keep calculating the value in column N as long as there is data in column E (or I)?

Thanks,

SW Try

Sub kTest1()
Dim Rng As Range, r As Range, fAdd As String

Const Find1 As Long = 1
Const rO As Long = 10
Const cO As Long = -4

Set Rng = Range("i3:i" & Range("e" & Rows.Count).End(xlUp).Row)

With Rng
Set r = .Find(Find1, LookIn:=xlValues)
If Not r Is Nothing Then
fAdd = r.Address
Do
'use either this
If r.Offset(rO).Row > Split(Split(Rng.Address(1, 0), ":")(1), "$")(1) Then Exit Do
r.Offset(rO, 5) = r.Offset(rO, cO) - r.Offset(, cO)
'or this
'If r.Offset(rO).Row > Split(Split(Rng.Address(1, 0), ":")(1), "$")(1) Then Exit Do
Set r = .FindNext(r.Offset(rO))
Loop While Not r Is Nothing And r.Address <> fAdd
End If
End With
End Sub
HTH

[Skywalker]

Krishna,

The lastest code works great. Seems to have sorted the error issue out.

Mark, I'll try re-running your version with the changes you suggest and let you know how I get on, though please don't use anymore of your time on the code as I think Krishna version works well for me.

Nope this is not homework, left university years ago! But it is related to a kinda academic non-work related analysis I'm doing to guage the relationship of an economic variable to other economic variables over time. So for example, what is the relationship between the trough in an economic variable (e.g consumer confidence) - denoted by a 1 and the change in another variable (e.g retail sales, a currency etc) over the next ten or 20 periods. To answer questions like is a trough in consumer confidence usually followed by postive or negative returns in the other variables.

I've done this type of analysis previously using econometric modelling but this is a slightly different approach specifically connecting troughs and peaks with subsequent changes/movements in other variables.

Looking at monthly data over a 30 year period gives you quite a few troughs in the data, hence lots of manual calculations to perform!

Thanks,

Skywalker

[Skywalker]

Krishna,

The lastest code works great. Seems to have sorted the error issue out.

Mark, I'll try re-running your version with the changes you suggest and let you know how I get on, though please don't use anymore of your time on the code as I think Krishna version works well for me.

Nope this is not homework, left university years ago! But it is related to a kinda academic non-work related analysis I'm doing to guage the relationship of an economic variable to other economic variables over time. So for example, what is the relationship between the trough in an economic variable (e.g consumer confidence) - denoted by a 1 and the change in another variable (e.g retail sales, a currency etc) over the next ten or 20 periods. To answer questions like is a trough in consumer confidence usually followed by postive or negative returns in the other variables.

I've done this type of analysis previously using econometric modelling but this is a slightly different approach specifically connecting troughs and peaks with subsequent changes/movements in other variables.

Looking at monthly data over a 30 year period gives you quite a few troughs in the data, hence lots of manual calculations to perform!

Thanks,

Skywalker