straw
09-30-2023, 09:20 PM
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. :bow:
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
31080
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
31080