Arrays and Looping to fill them

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
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)
ABCDEFG
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5TEAM6
214050608090

<tbody>
</tbody>

TO: (SHEET 2)
ABC
1WEEKTEAMSCORE
21TEAM140
3TEAM250
4TEAM360
5TEAM580
6TEAM690
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:
Code:
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
 
Last edited:
Tyron,

From what I am finding it seems like most people are very helpful when it comes to getting code, but they aren't able to explain why they are doing it that way or why it works.

If you would like, I can add comment lines to the macro code so that you can get an idea as to what the macro is doing?

Be back later.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hey hiker95,

That would be awesome.

I know that one of my confusions right of the bat is how you use single letters for variables/arrays in the for loop.

maybe if you could put verbose variable names that would probably take care of half the problem to make it easier to follow the code.

Unfortunately I haven't been programming for more than a couple of months so the streamline method that you are using, which I am sure is the norm that you learned to do leaves me scratching my head and pulling out hair. lol. Now I understand why some programmers end up going bald: Stress and Frustration. lol.

later

Ty
 
Upvote 0
Tyron,

I hope the following helps you to understand what the macro is doing.


See the comment lines that begin with the ' character for an explanation of one loop in the a array, in row 2:

Code:
'   ReorgDateV2 is the name of the macro
Sub ReorgDataV2()



' for my record keeping
'      the date of the macro
'          |     MrExcel thread number
'          |             |
' hiker95, 09/04/2014, ME803110



' it is a grod programming practice to define all your variables
'    this way if there was a problem with the macro code
'    another programmer could fix the problem
' create variables to hold
'   w1 = Sheets("TeamData")
'   |                w2 = Sheets("Scores")
'                    |
Dim w1 As Worksheet, w2 As Worksheet



' Variant arrays can hold all types of data
'   a is the Variant array that will hold all the information in w1 = Sheets("TeamData")
'   |             o is the Variant array that will hold the results
'   |             |    that will be written to in w2 = Sheets("Scores")
Dim a As Variant, o As Variant



'   i is the row counter for the a array
'   |          j is the row counter for the o array
Dim i As Long, j As Long



'   lr = last row, will find the last used row in Sheets("TeamData")
'   |           lc = last column, will find the last used column
'   |           |   in Sheets("TeamData") row 2
'   |           |           n will count how many cells in w1 Range("B2:F" & lr)
'   |           |           |   that contain numbers
'   |           |           |          n2 will count how many numbers there are
'   |           |           |          |      in a range for each week number
'   |           |           |          |      for row 2 w1 Range("B2:F2")
'   |           |           |          |           c is the column counter
'   |           |           |          |           |    from column 2 to the lc = 6
'   |           |           |          |           |          s is a switch
'   |           |           |          |           |          |    see description later
Dim lr As Long, lc As Long, n As Long, n2 As Long, c As Long, s As Long



' turn screen updating off will stop screen flicker, and, may make the macro run faster
Application.ScreenUpdating = False



' set the w1 variable to point to Sheets("ReamData")
' set the w2 variable to point to Sheets("Scores")
Set w1 = Sheets("TeamData")
Set w2 = Sheets("Scores")



' working in w1
With w1



' lr will find the last used row in column 1 = A for the Week column = A
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  
  
  
' lc will find the last used column in row 1, column = 6 = F
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column



' set the a Variant array to hold the information in w1
'   in Range
'             A1            F7
  a = .Range(.Cells(1, 1), .Cells(lr, lc))



' n will calculate the number of rows needed in the o array
'                    for Range  B2            F7
'                                                             + some extra rows
  n = Application.Count(.Range(.Cells(2, 2), .Cells(lr, lc))) + lr - 1



' create, ReDim, for re-dimension the o array
'       o(1 to n + 1, 1 to 3)
'         1 to n + 1 rows
'                     1 to 3 columns
  ReDim o(1 To n + 1, 1 To 3)
End With



'  at the beginning of the o array, the row counter j has not been initialized
'    so it is equal to 0 = zero
'  j = j + 1 makes j = 1
j = j + 1



' write the the o array, the header titles in row 1
'  o(j, 1) stands for column A
'                 o(j, 2) stands for column B
'                                   o(j, 3) stands for column C
o(j, 1) = "Week": o(j, 2) = "Team": o(j, 3) = "Score"



' lets loop thru the a array beginning in row 2 that contains the raw data
For i = 2 To lr



' ********** the following will look at just row 2 **********
' calculate the n2 variable to see how many numbers are in row 2
'                        w1.Range("B2:F2")
  n2 = Application.Count(w1.Range(w1.Cells(i, 2), w1.Cells(i, lc)))
  
  
  
' if n2 = 0 then only write to the o array the week number
  If n2 = 0 Then
  
  
  
'   increment the row counter for the o array by 1
    j = j + 1
    
    
    
'   write to the o array the week number
    o(j, 1) = a(i, 1)



' If n2 is greater that 0 = zero then
  ElseIf n2 > 0 Then
  
  
  
'   set the s switch to 0 = zero
    s = 0
    
    
    
'  loop thru row 2 in the a array
'  beginning in column c = 2 = column B to the lc = 6 = column F
    For c = 2 To lc
    
    
    
    
'     if a(i, c) is NOT blank
'        |
'        |             And s = 0 Then
      If a(i, c) <> "" And s = 0 Then
            
      
      
'       increment the o array row counter
        j = j + 1
        
        
        
'       write to the o array
'       o(j, 1) = Week number 1
        o(j, 1) = a(i, 1)
        
        
        
'       o(j, 2) = "Team1"
        o(j, 2) = a(1, c)
        
        
        
'       o(j, 3) = 50
        o(j, 3) = a(i, c)
        
        
        
'       set s = 1
        s = 1
        
        
               
'     ElseIf a(i, c) is not equal to a blank
'                          And s = 1 then
      ElseIf a(i, c) <> "" And s = 1 Then
      
      
      
'       increment the o array row counter
        j = j + 1
        
        
        
'       o(j, 2) = "Team2"
        o(j, 2) = a(1, c)
        
        
        
'       o(j, 3) = 60
        o(j, 3) = a(i, c)
      End If
      
      
      
'   loop to the next column in row 2
    Next c
  End If
  
  
  
' loop to the next row in the a array
Next i



' working in w2
With w2



'  clear the contents of the used range
  .UsedRange.ClearContents
  
  
  
'  Range("A1")
'             .Resize(n + 1 rows
'             |              3 = the number of columns
'             |              |    in the o array
'             |              |    write the
'             |              |          o array
  .Cells(1, 1).Resize(n + 1, 3).Value = o
  
  
  
' auto fit columns
'          1 = A
'                      3 = C
  .Columns(1).Resize(, 3).AutoFit
'  columns("A:C")
  
  
  
'  Activate/Select worksheet w2 = Sheets("Scores")
  .Activate
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hey hiker95,

Wow! I think I can only count on one hand the number of people who have taken the time to do some really good commenting. I am happy that I can count you as one of those people.

I am still digesting the information that you have given me. I want to read it like 10 or 20 times before I make a reply to it though. So I will probably reply to the message next week.

Thanks again for taking the time. I know that most people know what all this means, but on the behalf of all the beginners I say: Thank You!

later

Ty
 
Upvote 0
Since one of your stated goals was to learn about arrays and how to work with them I provided an alternative.
I too would be willing to comment my code but, like hiker, was waiting first to see if it did the task successfully.
Just wondering if you tried it (I have modified it slightly since first posted)?
 
Upvote 0
Since one of your stated goals was to learn about arrays and how to work with them I provided an alternative.
I too would be willing to comment my code but, like hiker, was waiting first to see if it did the task successfully.
Just wondering if you tried it (I have modified it slightly since first posted)?

Hi ,
I checked it just now and was "destroying it" with my green comment graffiti (as I do) just now to understand it.
. It definitely works
. And it is proving fairly easy to understand, - using the watch window I am following it as quickly or quicker as hiker's with the comments!
. But I would very much welcome to see your version with comments to compare with mine.
Alan.

P.s. Can you tell me how you preserved the Blue color etc. in the Code Tags ( I am sure you did not do that manually!)
Thanks
Alan.
 
Upvote 0
Tyron,

Thanks for the feedback.

You are very welcome, again. Glad I could help.

And, come back anytime.
 
Upvote 0
DocAElstein,

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

But, the will to learn should never disappear.

As you progress in your coding style, it will change based on what you can learn.

You will find ways to do the same thing, and, much faster, and, probably with shorter code.

Keep a good library of formulae, and, sample code, and, training links/material.

There are so many ways to accomplish the same end product.
 
Upvote 0
. It definitely works
I will wait to see if the OP confirms it works with their real data, which could be different to yours (& mine).



P.s. Can you tell me how you preserved the Blue color etc. in the Code Tags ( I am sure you did not do that manually!)
I have done so before, so I suggest that you refer back to this thread, particularly post #9 & #23.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top