Excel: Arrays and Looping to fill them


Hey Guys, I was wondering if you might be able to help me. I have been all over the web pulling my hair out on this one. It seems I can't find an informative answer on this. They all just seem to list code that does the job, but doesn't explain how it works. Goal: Grab data from sheet 1 that is listed horizontally and paste that information in sheet 2 vertically. FROM: (SHEET 1)

A B C D E F G
1 WEEK TEAM1 TEAM2 TEAM3 TEAM4 TEAM5 TEAM6
2 1 40 50 60 80 90
TO: (SHEET 2)
A B C
1 WEEK TEAM SCORE
2 1 TEAM1 40
3 TEAM2 50
4 TEAM3 60
5 TEAM5 80
6 TEAM6 90
7
8
9
If you notice above it omitted Team4 completely since there was no score for that week for that team. I am aware that I have to have a Multidimensional array and use a loop to gather both the heading data (Team1-6) and the score data. Here is my code so far:

Option Explicit
Sub FormatScores()
Dim TeamCount as Integer
Dim TeamScore as Integer
Dim VarTeamScore(6,6) As Variant
For TeamCount 0 to 6
  VarTeamScore(TeamCount) = Range(cells(1, TeamCount).value
  VarTeamScore(ScoreCount) = Range(cells(1, ScoreCount).Value
  TeamCount = TeamCount + 1
  ScoreCount = ScoreCount + 1
Next
End Sub
Any help would be great as the web has turned out to be very vague in explaining Arrays and using Loops with them. later Ty


This question generated 17 answers. To proceed to the answers, click here.

This thread is current as of September 03, 2014.


For more resources for Microsoft Excel