Explanation VBA code [Activate+Loop]

[Edmond]

Hello Everyone,

I am starting to spend some time learning VBA programming by trying to understand what others are doing.

I just ran across this code:

Columns("J:J").Select
Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

N = ActiveCell.Row
N1 = N
Cells(i, 11).Value = Cells(N, 2).Value
i = i + 1

Selection.FindNext(After:=ActiveCell).Activate
N = ActiveCell.Row

Do While N <> N1
Cells(i, 11).Value = Cells(N, 2).Value
Selection.FindNext(After:=ActiveCell).Activate
N = ActiveCell.Row
i = i + 1
Loop

I am trying to understand line by line:

Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Does this line mean that all cells equal to 1 are activated?

N1 = N Cells(i, 11).Value = Cells(N, 2).Value
i = i + 1

Selection.FindNext(After:=ActiveCell).Activate
N = ActiveCell.Row

I don't understand either the N1=N


I have run that Macro so I understand what it does. But line by line I'm not able to reproduce it because I don't understand all the line.

I hope my explanations were sufficiently clear.

Have a good day

Edmond

[offthelip]

I have annotated the code with comments which I hope will help you,
The code included an error the variable i was used before it was defined

Sub test()
' This selects column J
Columns("J:J").Select
' initialisation of I which was missing
i = 10
' This searches for the character 1 in the selects (columnj) and it will look in any part of the line
Selection.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
' this line pus the row number where the search found the first ocurence of 1 into the variable N
N = ActiveCell.Row
' this copies the value in N into another variable N1, so at this point they both have the same number in them
N1 = N
' This statement will cause an error because i is not defined at this point, what this statement is trying to do is copy the value in
' column B in the same row as the find statement above found into Column K wiht the row given by the variable i ( which you haven't defined so it is empty0
' this is solved by initalising I at the top which I have done ( i set it to 10)
Cells(i, 11).Value = Cells(N, 2).Value
' This increment the row number
i = i + 1
' this does the find again
Selection.FindNext(After:=ActiveCell).Activate
' as above
N = ActiveCell.Row
' this now does a loop and keeps on doing a find until eventually the find gets back to the beginning and finds the first row again
' when this happens N equals N1 which was the first row we found "1" on and so the loop exits exits
Do While N <> N1
' copy as above
Cells(i, 11).Value = Cells(N, 2).Value
' find again
Selection.FindNext(After:=ActiveCell).Activate
N = ActiveCell.Row
' increment the row to copy to
i = i + 1
Loop
End Sub

[Edmond]

Hey,

Thank you very much for you explanations. It helps a lot! The N=N1 was confusing but I have it now.

And I didn't copy every lines of the codes but it works

i = 1



NCusts = Range("A1").End(xlDown).Row - 1


Cells(2, 10).FormulaR1C1 = "=if(and(RC2<>"""",RC2<>R[-1]C2),1,"""")"
Range("J2").Select
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(NCusts - 1, 0))

That was the beginning.

Have a good day!

Edmond