VBA dictionary concept problem

[straw]

Hi all, I am following some sites to learn vba dictionary right now. However, some codes I can't understand. Please help to explain. Really appreciate your help.

Questions are inside "" after the code.

Please see codes below.

Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
    subject = Worksheets("Data").Cells(i, 3).Value
    score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
        targetRow = dict.Item(subject)      "At this time, dict.item(subject)=score or name, or both?"
    Else
        targetRow = 1                "Why does targetRow=1 here?"
    End If
Worksheets(subject).Cells(targetRow, 1) = name
    Worksheets(subject).Cells(targetRow, 2) = score
    dict.Item(subject) = targetRow + 1     
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False    "Does this code mean that go until the last filled row?"
Wend
End Sub

Sample.JPG

[arnelgp]

the code somewhat is incomplete. how does the each element in the dict gets Added, that is missing.

[straw]

Thanks arnelgp! the code misses the part of create 3 sheets named "English", "Physics", "Biology". After creating these 3 sheets, it can run perfectly. I got the whole tutorial from a website and I believe that's all codes they provided.

As an example here's a table of raw exam data by subject:

Name	Date	Subject	Score
Alex	04/02/2009	Biology	60.00
Alex	06/03/2009	English	60.00
Angela	04/02/2009	Biology	47.00
Angela	06/03/2009	Physics	47.00
Bharat	02/02/2009	English	64.00
Bharat	04/03/2009	English	64.00
Christine	03/02/2009	Physics	52.00
Christine	05/03/2009	Physics	52.00
George	03/02/2009	Physics	71.00
George	05/03/2009	English	71.00
Gilbert	03/02/2009	Physics	14.00
Gilbert	05/03/2009	Physics	14.00
Jane	02/02/2009	English	56.00
Jane	04/03/2009	Biology	56.00
Peter	02/02/2009	English	45.00
Peter	03/02/2009	Physics	52.00
Peter	04/03/2009	English	45.00
Peter	05/03/2009	Physics	52.00

The VBA code to do the processing looks like this:

Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
    subject = Worksheets("Data").Cells(i, 3).Value
    score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
    targetRow = dict.Item(subject)
    Else
    targetRow = 1
    End If
Worksheets(subject).Cells(targetRow, 1) = name
    Worksheets(subject).Cells(targetRow, 2) = score
    dict.Item(subject) = targetRow + 1
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False
Wend
End Sub

Running this allows us to process the data and produce a sheet that looks like this:

Alex	60
Bharat	64
Bharat	64
George	71
Jane	56
Peter	45
Peter	45

Thanks again!

[p45cal]

how does the each element in the dict gets Added

without actually opening the file to check, I think on this line:

dict.Item(subject) = targetRow + 1

(I think it's called implicit addition)

[arnelgp]

you are correct!