Menu
Ask a question Report

Question on VBA code [Solved/Closed]

Griever666 - Feb 28, 2017 at 01:45 AM - Latest reply:  Griever666
- Feb 28, 2017 at 11:49 AM
Excel Ask a question Report
Hello,

can someone explain this code to me? I have this file that has a macro button transferring all the information(KPI's to be exact) from a database worksheet to a viewing worksheet. On the viewing worksheet, you select a name of an employee and when you press the "load data" button, it transfers all the KPI for that specific employee. Any help would be great.

Private Sub cmdLoad_Click()
Dim i, j As Integer

j = wksPMP.Cells(4, 4) + 2

For i = 23 To 34
wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i - 20)
Next i

For i = 40 To 51
wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i - 25)
Next i

For i = 57 To 68
wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i - 30)
Next i

For i = 74 To 85
wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i - 35)
Next i

For i = 23 To 34
wksPMP.Cells(i, 8) = wksDatabase.Cells(j, i + 28)
Next i

For i = 40 To 51
wksPMP.Cells(i, 8) = wksDatabase.Cells(j, i + 23)
Next i

For i = 57 To 68
wksPMP.Cells(i, 8) = wksDatabase.Cells(j, i + 18)
Next i

For i = 74 To 85
wksPMP.Cells(i, 8) = wksDatabase.Cells(j, i + 13)
Next i

For i = 91 To 102
wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i + 8)
Next i

End Sub

Private Sub cmdSave_Click()
Dim i, j As Integer

j = wksPMP.Cells(4, 4) + 2

For i = 23 To 34
wksDatabase.Cells(j, i - 20) = wksPMP.Cells(i, 3)
Next i

For i = 40 To 51
wksDatabase.Cells(j, i - 25) = wksPMP.Cells(i, 3)
Next i

For i = 57 To 68
wksDatabase.Cells(j, i - 30) = wksPMP.Cells(i, 3)
Next i

For i = 74 To 85
wksDatabase.Cells(j, i - 35) = wksPMP.Cells(i, 3)
Next i

For i = 23 To 34
wksDatabase.Cells(j, i + 28) = wksPMP.Cells(i, 8)
Next i

For i = 40 To 51
wksDatabase.Cells(j, i + 23) = wksPMP.Cells(i, 8)
Next i

For i = 57 To 68
wksDatabase.Cells(j, i + 18) = wksPMP.Cells(i, 8)
Next i

For i = 74 To 85
wksDatabase.Cells(j, i + 13) = wksPMP.Cells(i, 8)
Next i

End Sub


See more 

2 replies

Report Respond to TrowaD
TrowaD 2355 Posts Sunday September 12, 2010Registration dateModeratorStatus May 17, 2018 Last seen - Feb 28, 2017 at 11:37 AM
0
Helpful
Hi Griever666, 

j = wksPMP.Cells(4, 4) + 2

Assigning a number to j. j will be the number found on the Viewing sheet cell D4 (=cells(4,4)) and then 2 is added to that number. 

For i = 23 To 34

A loop is initiated. With each loop, i is increased by 1. Starting from 23 and ending on 34. 

wksPMP.Cells(i, 3) = wksDatabase.Cells(j, i - 20)

The viewing sheet cell C23 will be the same as the value on the database sheet row j (determined earlier) and column i-20 (23-20) = 3 = C. 

Next i

Go to the start of the loop.

Best regards,
Trowa
Report
Griever666 - Feb 28, 2017 at 11:49 AM
You sir are the boss! Thanks much!
Ask a question