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:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try:

Code:
Option Explicit

Sub FormatScores()

Dim TeamCount as Integer
Dim ScoreCount as Integer

Dim VarTeamScore(6,6) As Variant

ScoreCount=0

For TeamCount 0 to 6
  VarTeamScore(TeamCount) = Range(cells(1, TeamCount).value
  VarTeamScore(ScoreCount) = Range(cells(1, ScoreCount).Value
  ScoreCount = ScoreCount + 1
Next

End Sub
 
Upvote 0
Oops, just noticed you started with TeamCount and ScoreCount being zero, then try using them to access cells. Zero is invalid for row or columns, so modified again:

Code:
Option Explicit

Sub FormatScores()

Dim TeamCount as Integer
Dim ScoreCount as Integer

Dim VarTeamScore(6,6) As Variant

ScoreCount=1

For TeamCount 1 to 6
  VarTeamScore(TeamCount) = Range(cells(1, TeamCount).value
  VarTeamScore(ScoreCount) = Range(cells(1, ScoreCount).Value
  ScoreCount = ScoreCount + 1
Next

End Sub
 
Upvote 0
Hey mjbeam,

Thanks for the quick response. I seem to be getting a compile error.

Error: Wrong number of dimensions

Also, as a side question on the code - Since I have 7 values that I need to store for both the TeamCount and ScoreCount should I change from "1 to 6" to "1 to 7" and change the array to indicate "7, 7"?

later

Ty
 
Upvote 0
Why do you need to store these values in arrays? Are you just wanting to move them from one sheet to another?
 
Upvote 0
Tyron,

The macro should adjust to the varying number of rows, and, columns in worksheet SHEET 1.


Sample raw data in worksheet SHEET 1:


Excel 2007
ABCDEFG
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5TEAM6
214050608090
3
SHEET 1


After the macro (using two arrays in memory) in worksheet SHEET 2:


Excel 2007
ABC
1WEEKTEAMSCORE
21TEAM140
3TEAM250
4TEAM360
5TEAM580
6TEAM690
7
SHEET 2


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ReorgData()
' hiker95, 09/03/2014, ME803110
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, n As Long, n2 As Long, c As Long
Application.ScreenUpdating = False
Set w1 = Sheets("SHEET 1")
Set w2 = Sheets("SHEET 2")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.Count(.Range(.Cells(2, 2), .Cells(lr, lc))) + lr - 1
  ReDim o(1 To n + 1, 1 To 3)
End With
j = j + 1
o(j, 1) = "WEEK"
o(j, 2) = "TEAM"
o(j, 3) = "SCORE"
For i = 2 To lr
  n2 = Application.Count(w1.Range(w1.Cells(i, 2), w1.Cells(i, lc)))
  If n2 = 0 Then
    j = j + 1
    o(j, 1) = a(i, 1)
  ElseIf n2 > 1 Then
    For c = 2 To lc
      If a(i, c) <> "" Then
        If c = 2 Then
          j = j + 1
          o(j, 1) = a(i, 1)
          o(j, 2) = a(1, c)
          o(j, 3) = a(i, c)
        ElseIf c > 2 Then
          j = j + 1
          o(j, 2) = a(1, c)
          o(j, 3) = a(i, c)
        End If
      End If
    Next c
  End If
Next i
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n + 1, 3).Value = o
  .Columns(1).Resize(, 3).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
Tyron,

If we started with this:


Excel 2007
ABCDEFG
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5TEAM6
21
324050608090
4
SHEET 1


The same macro would produce this:


Excel 2007
ABC
1WEEKTEAMSCORE
21
32TEAM140
4TEAM250
5TEAM360
6TEAM580
7TEAM690
8
SHEET 2
 
Upvote 0
Hey mjbeam,

That is a good question. As you know simply I could just use the macro recorder to just create a simple copy paste situation.

My goal is to learn how to use an array, but beyond that I would like to understand how it works and why.

My goal is definitely pretty lofty, but I think that with trying to manipulate values and only allowing myself to use arrays to do it I will be able to learn some.

Most of the Array samples and tutorials do not address the things I am trying to learn. No this is not a homework assignment. I am not in a class so your code and help will not cheat me through school.

later

Ty
 
Upvote 0
Tyron,

If we started with this:

Excel 2007
ABCDEFG
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5TEAM6
21
324050608090
4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
SHEET 1



The same macro would produce this:

Excel 2007
ABC
1WEEKTEAMSCORE
21
32TEAM140
4TEAM250
5TEAM360
6TEAM580
7TEAM690
8

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
SHEET 2

Hello hiker95,

First, I guess it is my fault for assuming that 2007 would also be completely compatible with 2013.
I had encountered two errors regarding the designating of the two worksheets. This happened because I guess the Sheet name designation is a bit different. Your code line was:
Code:
Set w1 = Sheets("SHEET 1")
Set w2 = Sheets("SHEET 2")
In 2013 I guess this takes the name of the sheet. So for it to work in mine I had to change to the following:
Code:
Set w1 = Sheets("Sheet1") 'This is the automatic unchanged name in 2013
Set w2 = Sheets("Scores")  'I had changed the name of the worksheet for
                                         'testing purposes
I created a module 2 in order to put it in and works like a charm. Thank you. Unfortunately, now I have to bombard you with questions though. lol.

1. Is it safe to say that you, in the interest of good programming, purposely define the variables at the beginning this way as to group variables by same type and to minimize the amount of times you have to type in the word "Dim"?

2. Is it always good practice to have:
Code:
Application.ScreenUpdating = False
.
.
.
Application.ScreenUpdating = True
in your code once you make sure that it does what you want it to do in order to lessen the amount of processing time?

3. Is it normal to limit your variable and array names to one or two letters in order to streamline the code? Or is it just a matter of lessening the keystrokes to create the code?

4. what is "(xlUP)" and what does it do?

5. The reason why you ReDim the array "o" to say:
Code:
ReDim o(1 To n + 1, 1 To 3)
is so that it indicates that there will be no value to take the "0" location within a normal array since it is referring to Cell location whereas a "0" would not be recognized? Or is there another reason?

6. I notice that you have included the column headings as static values. You have done this because there are only a couple correct? Had there been more Headings, say 10+, would it be more beneficial to include an additional Loop and an extra array?

7. When it gets down to the For and If loops I kind of get lost. I do see that it checks for blank entries on scores and skips the team if it is blank. Is there any way that you could comment each line so that I can understand what VBA is doing at each line from first line of code to last?

8. I was suprised to see that you added cell formatting in sheet 2. That is a bonus for me as I was considering having to do something like this. Is it good to do this when transferring data to alternate sheets like this?

9. Is it better to activate the sheet after formatting the second sheet or before? Or is this just so that it ends on the second sheet so you can see the results?

Thank you in advance. I await your reply Sensei.

Ty
 
Upvote 0
Tyron,

The macro should adjust to the varying number of rows, and, columns in worksheet SHEET 1.


Sample raw data in worksheet SHEET 1:

Excel 2007
ABCDEFG
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5TEAM6
214050608090
3

<tbody>
</tbody>
SHEET 1



After the macro (using two arrays in memory) in worksheet SHEET 2:

Excel 2007
ABC
1WEEKTEAMSCORE
21TEAM140
3TEAM250
4TEAM360
5TEAM580
6TEAM690
7

<tbody>
</tbody>
SHEET 2



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ReorgData()
' hiker95, 09/03/2014, ME803110
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, n As Long, n2 As Long, c As Long
Application.ScreenUpdating = False
Set w1 = Sheets("SHEET 1")
Set w2 = Sheets("SHEET 2")
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.Count(.Range(.Cells(2, 2), .Cells(lr, lc))) + lr - 1
  ReDim o(1 To n + 1, 1 To 3)
End With
j = j + 1
o(j, 1) = "WEEK"
o(j, 2) = "TEAM"
o(j, 3) = "SCORE"
For i = 2 To lr
  n2 = Application.Count(w1.Range(w1.Cells(i, 2), w1.Cells(i, lc)))
  If n2 = 0 Then
    j = j + 1
    o(j, 1) = a(i, 1)
  ElseIf n2 > 1 Then
    For c = 2 To lc
      If a(i, c) <> "" Then
        If c = 2 Then
          j = j + 1
          o(j, 1) = a(i, 1)
          o(j, 2) = a(1, c)
          o(j, 3) = a(i, c)
        ElseIf c > 2 Then
          j = j + 1
          o(j, 2) = a(1, c)
          o(j, 3) = a(i, c)
        End If
      End If
    Next c
  End If
Next i
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n + 1, 3).Value = o
  .Columns(1).Resize(, 3).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

Hey hiker95,

It appears that if the data for the first two teams are missing it doesn't indicate the week in which they scores are for.
ABCDEF
1WEEKTEAM1TEAM2TEAM3TEAM4TEAM5
2150608090
3236101008652
43501812568
54118526

<tbody>
</tbody>










The following is are the results after running the macro:
ABC
1WEEKTEAMSCORE
21TEAM150
3TEAM260
4TEAM480
5TEAM590
62TEAM136
7TEAM210
8TEAM3100
9TEAM486
10TEAM552
113TEAM150
12TEAM218
13TEAM3125
15TEAM568
16TEAM3118
17TEAM45
18TEAM526

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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