Arrays and Looping to fill them

[Tyron]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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

 

[DocAElstein]

......



I have done so before, so I suggest that you refer back .......

Hi ,
. I remember everything you said there, you were very informative thanks.
. In #9 you said change color in the code tag manually or use the HTML maker. That suggests you did that manually as you are using Code Tags? Or did you paste the html code in the code tag: I have had limited success with that method.
. In #23 you talked again about the html code maker, but began discussing your using of the standard editor where everything is in HTML code. May I infer from that, that you used a third approach? -. That is to say you are always in the standard editor and write everything in html code? (If so… WOW!)

Thanks, Alan
P.s. I used Tyron’s provided sample data in the Thread.

 

[DocAElstein]

DocAElstein,

We have all started, been, where you are now.........

Hi, thanks for that..
. My problem is I am very keen to learn but cannot always find the time to practice. When I do come on the board I try to give something back by answering the threads I am able to. That is why it is so great that people like you Peter and others give so much time voluntarily on the Board. I, we, am very grateful. And I know how hard and frustrating it is when you are so experienced to “come down” to wot must seem to you a ridiculously low level of intelligence!!!(At least in my case!)

P,s, Your commenting was amazing and so clearly written. Just one very minor thing. If you could use more than one letter for variables that would be great. If necessary I can change that further with the search and replace function (Like lr is easy to search and change to LastRow). But single letters are very difficult as the letter appears in other places as well. But it is a very small Point. I am not lazy and can of course, (as I sometimes do), go through one by one in the occurrences of a single variable and change if I want to.
. I personally am finding now that with variables written a bit more fully to describe wot they are, along with setting a watch in the watch window, (along with using the extended monitor graphic with two monitors, so I have spreadsheet and VB window both at the same tome on different screens), then with all that and then stepping with F8 I can go through a code quite quickly.

 

[Peter_SSs]

Or did you paste the html code in the code tag: I have had limited success with that method.

Yes, that is what I usually do. It has the advantage, in my opinion, of looking better and also adds scroll bars to the code window when required.

In #23 you talked again about the html code maker, but began discussing your using of the standard editor where everything is in HTML code. May I infer from that, that you used a third approach? -. That is to say you are always in the standard editor and write everything in html code? (If so… WOW!)

I do use Standard Editor - Extra Formatting Controls, but no I don't write in HTML code. I type my standard text normally and (usually) post vba code using the VBHTML Maker as described above.

P.s. I used Tyron’s provided sample data in the Thread.

So did I but I hardly think that would be his entire real data so there could be further variations or formatting could be different either of which may cause the code to not always perform as desired.

 

[DocAElstein]

Yes, that is what I usually do. It has the advantage, in my opinion, of looking better and also adds scroll bars to the code window when required.

.......

I agree. That is the best method. As I mentioned it did not always work when I tried it. So I will do some more tests when I can find the time.
Thanks for the quick Reply. It cleared that up, - as a beginner one gets confued very easilly.
Thanks again for all your efforts (and patience with me!)
Alan

 

[hiker95]

DocAElstein,

Hi, thanks for that..

You are very welcome.

Your commenting was amazing and so clearly written. Just one very minor thing. If you could use more than one letter for variables that would be great.

In the beginning I used longer variable names, but, now, I prefer more compact code - just a personal preference.

Have a great day, and, come back anytime.

 

[Tyron]

I will wait to see if the OP confirms it works with their real data, which could be different to yours (& mine).



I have done so before, so I suggest that you refer back to this thread, particularly post #9 & #23.

Hey Peter_SSs,

Sorry about that. RL is a bit hectic for the next two weeks so my free time is minimal at the moment.

I have taken the code from your post #20 and applied it to my raw team data and I have achieved the same results.

I am curious as to the approach differences. In my opinon no one way is the correct way. This said I am able to keep my mind open to different programming styles.

I am definitely interested in the comments that go to this code as I am sure many others would be as well.

I am slowly creating my code snippet data base. But as with any type of notes/study materials - eventually they become more of a crutch. I hope with everyones help to be able to evolve to the point where I can exceed this and be able to program from scratch based on my knowledge of how things work.

Thanks again.

Ty

 

[Peter_SSs]

In my opinon no one way is the correct way.

I would agree with that. A way that you understand and works in a way that is fast enough for your needs is sufficient. Your choice.

I am definitely interested in the comments that go to this code as I am sure many others would be as well.

Here they are

Sub ArrangeScores()
  'Some variables obvious, w = rows where weeks are recorded, t = columns where temas are recorded
  'k =  a placemarker to record what row of the results we are up to
  Dim NumTeams As Long, NumWeeks As Long, w As Long, t As Long, k As Long
  'These will be Arrays to hold original data and the results
  Dim Data, Results
  
  'Read into the Data array all values in the region of a1
  'That is, expand from A1 until a blank column meets a blank row
  Data = Sheets("TeamData").Range("A1").CurrentRegion.Value
  'No. of teams = horizontal size of this array - 1 for the 'week' column
  NumTeams = UBound(Data, 2) - 1
  'No. of weeks = vertical size of this array -1 for the 'team' row
  NumWeeks = UBound(Data, 1) - 1
  'We don't know how many rows we will need for the results so make an array
  'that is sure to be big enough (No. of teams x No. of weeks)
  ReDim Results(1 To NumTeams * NumWeeks, 1 To 3)
  'Set ke to initial value - row 1 of results
  k = 1
  'Work through the rows of the data array starting from row 2
  'which is the first 'week' row
  For w = 2 To NumWeeks + 1
    'Put the week no. into the the 1st column of the results array
    Results(k, 1) = Data(w, 1)
    'Now work through the 'team' values on that row
    For t = 2 To NumTeams + 1
      'If the team value is not blank we have some results to record
      If Data(w, t) <> "" Then
        'The team name comes from row 1 of that column
        Results(k, 2) = Data(1, t)
        'The score comes from the current row 7 column
        Results(k, 3) = Data(w, t)
        ' Increment k ready for the next result row
        k = k + 1
      End If
    'Repeat for all teams on that row
    Next t
    'Increment k again if the previous week had no team values to record
    If Results(k, 1) <> "" Then k = k + 1
  'Repeat for each week (ie each row)
  Next w
  'On the Scores sheet
  With Sheets("Scores")
    'Clear any existing data
    .UsedRange.ClearContents
    'Write the Results array, starting at cell A2
    .Range("A2").Resize(NumTeams * NumWeeks, 3).Value = Results
    'Put the headings in row 1
    .Range("A1:C1").Value = Array("Week", "Team", "Score")
  End With
End Sub