Help understanding code

[louisepr]

Hi. I am new to VBA and would appreciate some assistance if its not too much trouble. When asked if I can knew macros, my answer was "yes, I can record them and do basic editing". I have been provided with a macro with no comments that appears to be straight programming and I am struggling with a couple of processes. . I get that it is "ignore top row in workbook named test and then it is defining the range as n. Then I get a bit lost on what its doing

row1 = 2
With Workbooks(test).ActiveSheet
For n = 2 To lastrowtest
If .Cells(n - 1, 1) <> .Cells(n, 1) Then
Cells(row1, 1) = .Cells(n, 1)
Cells(row1, 2) = .Cells(n, 2)
Cells(row1, 3) = .Cells(n, 3)
row1 = row1 + 1
End If
Next n
End With

 

[GWteB]

The content of a cell in column A (from row 1) is compared with the cell immediately below it. If both are the same, nothing else will actually happen, because the cells on the directly underlying row of columns A to C are copied to themselves (and so on ....). If the contents are not equal there's nothing to do.

 

[cooper645]

row1 = 2 'Set the variable row1 to the value 2

With Workbooks(test).ActiveSheet 'between the open with and closing end with, operate on the active sheet in the workbook "test"

For n = 2 To lastrowtest 'Loop through the code assigning n the value 2, then increase n by 1 until wereach the lastrowtest variable (likely your last row of data)
    If .Cells(n - 1, 1) <> .Cells(n, 1) Then 'Loop1_IF A1 not equal to A2 THEN,    Loop2_IF A2 not equal to A3 THEN
    Cells(row1, 1) = .Cells(n, 1) 'Loop1_A2 = A2,    Loop2_A3 = A3
    Cells(row1, 2) = .Cells(n, 2) 'Loop1_B2 =B2,     Loop2_B3 = B3
    Cells(row1, 3) = .Cells(n, 3) 'Loop1_C2 = C2,    Loop2_C3 = C3
    row1 = row1 + 1 'add 1 to row1 variable so row1 variable becomes 3
    End If 'End if statement
Next n 'go to start of loop and assign the next n variable (so n=3 for the next loop)

End With 'Closing With

 

[louisepr]

oh, wow. Thank you so much - if you have time, could you please also help me with the code below?


For loopnames = 3 To row1 - 1
staffno = Cells(loopnames, 1)
Set c = Workbooks(TEST2).ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole)
If Not c Is Nothing Then
Cells(loopnames, 4) = Workbooks(TEST2).ActiveSheet.Cells(c.Row, 8) / 100
End If
Next loopnames

Workbooks(TEST).Activate
For loopnames = 3 To lastrowTEST
staffno = Cells(loopnames, 1)
code = Cells(loopnames, 5)
amount = Cells(loopnames, 6)

Set c = Workbooks(TEST3).ActiveSheet.Range("B:B").Find(code, lookat:=xlWhole)
If Not c Is Nothing Then
Set d = ThisWorkbook.ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole)
If Not d Is Nothing Then
d = d.Row
ee = ThisWorkbook.ActiveSheet.Cells(d, 4) * amount
ThisWorkbook.ActiveSheet.Cells(d, 5) = ThisWorkbook.ActiveSheet.Cells(d, 5) + ee
End If
End If
Next loopnames

Workbooks(TEST).Activate
For loopnames = 3 To row1 - 1
staffno = ThisWorkbook.ActiveSheet.Cells(loopnames, 1)

For n = 3 To lastrowTEST
If Cells(n, 1) = staffno And Cells(n, 4) = "C" Then
ThisWorkbook.ActiveSheet.Cells(loopnames, 6) = ThisWorkbook.ActiveSheet.Cells(loopnames, 6) + Cells(n, 6)
End If
Next n
Next loopnames

 

[GWteB]

I recommend that you familiarize yourself with the Visual Basic Editor (VBE), then you can try the code yourself step by step and see what it does. You encounter some problems immediately, because some quotes are missing here and there (just like in the code of your post #1 by the way ...)
The VBE can be opened from Excel with ALT F11 key. Within VBE open a module pane (Menu> Insert> Module) and paste your code in it. Open a control pane (Menu> View> Locals Window) to watch how variables evaluate. Have your worksheet also on your desktop (Excel window and VBE window next to each other), click within the module pane of the VBE somewhere between SUB and END SUB, press F8 key multiple times and watch what happens.

 

[louisepr]

I recommend that you familiarize yourself with the Visual Basic Editor (VBE), then you can try the code yourself step by step and see what it does. You encounter some problems immediately, because some quotes are missing here and there (just like in the code of your post #1 by the way ...)
The VBE can be opened from Excel with ALT F11 key. Within VBE open a module pane (Menu> Insert> Module) and paste your code in it. Open a control pane (Menu> View> Locals Window) to watch how variables evaluate. Have your worksheet also on your desktop (Excel window and VBE window next to each other), click within the module pane of the VBE somewhere between SUB and END SUB, press F8 key multiple times and watch what happens.

ok, thank you

 

[cooper645]

was also going to suggest using the locals window whilst watching the code run. Extremely good way to learn

For loopnames = 3 To row1 - 1 'new loop - Variable loopnames = 3 to whatever row1 variable is
staffno = Cells(loopnames, 1) 'Loop1_set staffno to value of A3
Set c = Workbooks(test2).ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole) 'Set c =range of staffno
If Not c Is Nothing Then 'if c isnt blank then
Cells(loopnames, 4) = Workbooks(test2).ActiveSheet.Cells(c.Row, 8) / 100 'D3 = staffno row column H value /100
End If
Next loopnames 'next loop

Workbooks(test).Activate
For loopnames = 3 To lastrowTEST 'another loop opened
staffno = Cells(loopnames, 1) 'staffno = A3 -first loop, A4 second....
code = Cells(loopnames, 5) 'code = E3 -first loop, E4 second....
amount = Cells(loopnames, 6) 'amount = F3 -first loop,F4 second....

Set c = Workbooks(TEST3).ActiveSheet.Range("B:B").Find(code, lookat:=xlWhole) 'findrange c = code variable in column B
If Not c Is Nothing Then 'If not blank
Set d = ThisWorkbook.ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole) 'find range d = staffno variable in column A
If Not d Is Nothing Then 'if not blank
d = d.Row 'd = row number of variable d (staffno row)
ee = ThisWorkbook.ActiveSheet.Cells(d, 4) * amount 'ee =D(staffno row) * amount variable
ThisWorkbook.ActiveSheet.Cells(d, 5) = ThisWorkbook.ActiveSheet.Cells(d, 5) + ee 'E(staffno row) =E(Staff no row) value +ee variable
End If
End If
Next loopnames

Workbooks(test).Activate
For loopnames = 3 To row1 - 1 'another loop
staffno = ThisWorkbook.ActiveSheet.Cells(loopnames, 1) 'staffno variable= A3 (loop1), A4 loop2......

For n = 3 To lastrowTEST 'loop in a loop 3 to lastrowTEST variable
If Cells(n, 1) = staffno And Cells(n, 4) = "C" Then 'if A3 = staffno AND D3 = "C" then
ThisWorkbook.ActiveSheet.Cells(loopnames, 6) = ThisWorkbook.ActiveSheet.Cells(loopnames, 6) + Cells(n, 6) 'F3 = F3 value +F3
End If
Next n
Next loopnames
End Sub

 

[GWteB]

ok, thank you

You are welcome!
Perhaps a brief guide of the VBE might help you along.

Visual Basic Editor Tutorial for Excel - How to use the VBE? - Analyst Cave

 

[louisepr]

You are welcome!
Perhaps a brief guide of the VBE might help you along.

Visual Basic Editor Tutorial for Excel - How to use the VBE? - Analyst Cave

great, I'll check it out. I look forward to learning more

 

[louisepr]

was also going to suggest using the locals window whilst watching the code run. Extremely good way to learn

For loopnames = 3 To row1 - 1 'new loop - Variable loopnames = 3 to whatever row1 variable is
staffno = Cells(loopnames, 1) 'Loop1_set staffno to value of A3
Set c = Workbooks(test2).ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole) 'Set c =range of staffno
If Not c Is Nothing Then 'if c isnt blank then
Cells(loopnames, 4) = Workbooks(test2).ActiveSheet.Cells(c.Row, 8) / 100 'D3 = staffno row column H value /100
End If
Next loopnames 'next loop

Workbooks(test).Activate
For loopnames = 3 To lastrowTEST 'another loop opened
staffno = Cells(loopnames, 1) 'staffno = A3 -first loop, A4 second....
code = Cells(loopnames, 5) 'code = E3 -first loop, E4 second....
amount = Cells(loopnames, 6) 'amount = F3 -first loop,F4 second....

Set c = Workbooks(TEST3).ActiveSheet.Range("B:B").Find(code, lookat:=xlWhole) 'findrange c = code variable in column B
If Not c Is Nothing Then 'If not blank
Set d = ThisWorkbook.ActiveSheet.Range("A:A").Find(staffno, lookat:=xlWhole) 'find range d = staffno variable in column A
If Not d Is Nothing Then 'if not blank
d = d.Row 'd = row number of variable d (staffno row)
ee = ThisWorkbook.ActiveSheet.Cells(d, 4) * amount 'ee =D(staffno row) * amount variable
ThisWorkbook.ActiveSheet.Cells(d, 5) = ThisWorkbook.ActiveSheet.Cells(d, 5) + ee 'E(staffno row) =E(Staff no row) value +ee variable
End If
End If
Next loopnames

Workbooks(test).Activate
For loopnames = 3 To row1 - 1 'another loop
staffno = ThisWorkbook.ActiveSheet.Cells(loopnames, 1) 'staffno variable= A3 (loop1), A4 loop2......

For n = 3 To lastrowTEST 'loop in a loop 3 to lastrowTEST variable
If Cells(n, 1) = staffno And Cells(n, 4) = "C" Then 'if A3 = staffno AND D3 = "C" then
ThisWorkbook.ActiveSheet.Cells(loopnames, 6) = ThisWorkbook.ActiveSheet.Cells(loopnames, 6) + Cells(n, 6) 'F3 = F3 value +F3
End If
Next n
Next loopnames
End Sub

Thank you It was very indimidating