For each loop in VBA

[JohanR]

Hey!

I'm quite new to VBA and would really appreciate some help to solve a problem I ran into. I have a dataset that looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]SCHOOL[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Johnny School[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Inga[/TD]
[TD]Jimmy School[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Johnny[/TD]
[TD]Carrie School[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]Beth[/TD]
[TD]Carrie School[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Johnny School[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Jimmy School[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]


This is just a small sample of my data. There are like 500 different schools in there and I would like to loop through all unique values in the school column. I'll then use it to create a pdf with scores for each school. I'll look into that procedure at a later stage, but for now I need to know how to set up a proper loop.

I've looked at various examples and I understand that I need to declare an array and then use a "for each" loop.

But I don't really understand how to declare an array from the unique values in a column. I don't want to write all the school names down. Can someone help me out?

Thanks in advance!

Johan

 

[Peter_SSs]

Welcome to the MrExcel board!

As far as I know, there is no direct way to get the unique names into an array. I would normally use Advanced Filter to create the unique values elsewhere on the sheet then load the values from there into an array. Another way id to use a dictionary object to build the unique list.

However, before any of that, do you really need to do all that? Could you perhaps create a Pivot Table first (part of that process is that Excel builds the unique list for you) and have the Pivot Table sum each school's score (or calculate minimum, maximum or whatever you want)? Then you could use the PT results to do whatever else you want to do.
Subtotals is another built-in feature that may be some use.
Just some thoughts.

 

[JohanR]

Thanks for replying Peter!

Actually I was using a pivot table and created a pdf (containing average results for all schools) for every year with a "for to loop". The code below worked very well for that purpose. But then I ran into trouble when I wanted to loop over school instead of year. I don't know how to change the code below to loop thoughout a list of names instead of years. Any ideas?

I would then want to use this pivot table to loop over a list of schools.

[TABLE="width: 500"]
<tbody>[TR]
[TD]SCHOOL[/TD]
[TD]SCORE[/TD]
[/TR]
[TR]
[TD]Johnny School[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Jimmy School[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Carrie School[/TD]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]

Sub ReportFiltering()
Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("Pivottabell2").PivotFields("Year")

' Starting to loop
Startnumber = 2012
EndNumber = 2015

For Startnumber = 2012 To EndNumber

'Clear Out Any Previous Filtering
  pf.ClearAllFilters

'Choose every year
  pf.CurrentPage = Startnumber
   
'Select and copy
  ActiveSheet.PivotTables("Pivottabell2").TableRange1.Select
  ActiveSheet.PivotTables("Pivottabell2").TableRange1.Copy
  
  'Create a new sheet and paste the results in new sheet
  Sheets.Add.Name = "Year " & Startnumber
    
  ActiveSheet.Paste
  Application.CutCopyMode = False

  'Activate the original sheet.
  Sheets("Blad1").Select
  
Next Startnumber

End Sub

 

[Peter_SSs]

See if you can adapt this to your use. Test in a fresh workbook.
Set up that sample data as shown in columns A:C

Run the code below. It should produce the results shown in column F.

If your list in columns A:C is very big (eg many thousands of rows) then we could do it a bit faster than this. I've coded it this way as I think simpler for a "new to vba" coder to grasp.

Sub GetNamesAndLoopThrough()
  Dim dict As Object
  Dim cel As Range
  Dim ky As Variant
  
  'Establish dictionary object
  Set dict = CreateObject("Scripting.Dictionary")
  'Ensure it is not Case-sensitive
  dict.CompareMode = 1
  'Put the school names sinto dictionary, they will not be repeated.
  For Each cel In Range("B2", Range("B" & Rows.Count).End(xlUp))
    dict(cel.Value) = 1 '(The 1 isn't important, we just needed something)
  Next cel
  
  'Just to demo cycling through the results
  For Each ky In dict.Keys
    Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ky
  Next ky
End Sub

Excel Workbook
	A	B	C	D	E	F
1	NAME	SCHOOL	SCORE
2	Lisa	Johnny School	30			Johnny School
3	Inga	Jimmy School	35			Jimmy School
4	Johnny	Carrie School	28			Carrie School
5	Beth	Carrie School	32
6	James	Johnny School	35
7	Sarah	Jimmy School	40
Unique List

 

[Peter_SSs]

Say you wanted the unique list and total score. Of course if the data was like the previous sample, then you directly use a Pivot Table. However, here is some sample code to show one way to do it by creating the unique list and summing scores as we go.

Sub GetNamesAndAddScores()
  Dim dict As Object
  Dim ary As Variant
  Dim i As Long
  
  'Establish dictionary object
  Set dict = CreateObject("Scripting.Dictionary")
  'Ensure it is not Case-sensitive
  dict.CompareMode = 1
  'Put the school names & scores into array first (faster)
  ary = Range("B2", Range("C" & Rows.Count).End(xlUp)).Value
  
  'Put names into dict & add scores as we go
  For i = 1 To UBound(ary)
    'If school is already in dict, add the new score to previous total
    If dict.Exists(ary(i, 1)) Then
      dict(ary(i, 1)) = dict(ary(i, 1)) + ary(i, 2)
    Else
      'add the new name and initial score to dict
      dict.Add ary(i, 1), ary(i, 2)
    End If
  Next i
  
  'Just to demo the results
  Range("F2").Resize(dict.Count, 2).Value = Application.Transpose(Array(dict.Keys, dict.Items))
End Sub

Data & Results:

Excel Workbook
	A	B	C	D	E	F	G
1	NAME	SCHOOL	SCORE
2	Lisa	Johnny School	30			Johnny School	65
3	Inga	Jimmy School	35			Jimmy School	75
4	Johnny	Carrie School	28			Carrie School	60
5	Beth	Carrie School	32
6	James	Johnny School	35
7	Sarah	Jimmy School	40
Unique List

 

[Marcelo Branco]

Hi Peter,

I think that is possible to simplify this part

For i = 1 To UBound(ary)
    'If school is already in dict, add the new score to previous total
    If dict.Exists(ary(i, 1)) Then
      dict(ary(i, 1)) = dict(ary(i, 1)) + ary(i, 2)
    Else
      'add the new name and initial score to dict
      dict.Add ary(i, 1), ary(i, 2)
    End If
Next i

to

For i = 1 To UBound(ary)
  dict(ary(i, 1)) = dict(ary(i, 1)) + ary(i, 2)
Next i

M.

 

[JohanR]

Thanks a lot Peter!

I merged your code with what I already had and was able to create a new result sheet for every school in my list. Very useful!

Johan

 

[Peter_SSs]

Hi Peter,

I think that is possible to simplify this part

Quite right Marcelo, thanks for picking me up.

 

[Peter_SSs]

Thanks a lot Peter!

I merged your code with what I already had and was able to create a new result sheet for every school in my list. Very useful!

Johan

Cheers. Thanks for letting us know.

 

[Marcelo Branco]

Quite right Marcelo, thanks for picking me up.

The first time I tried this construct, I was surprised that it worked. It's a bit strange that it works for the first instance of a particular dictionary key. How and why it works, honestly I do not know.

M.