Wanting to change the range in a Macro

[MrSams]

You all have been real superstars in helping us. Hopefully this will be an easy 1. We are wanting to change the range on our Macro for placement of the data were getting and can not figure out how to do so.

Presently when we run the Macro it starts with data placement on the rows like this:

Ball 1 row 3
Ball 2 row 51
Ball 3 row 101
Ball 4 row 151
Ball 5 row 201
Ball 6 row 251

We would like it to be like this

Ball 1 row 3
Ball 2 row 3000
Ball 3 row 6000
Ball 4 row 9000
Ball 5 row 12000
Ball 6 row 15000

This is 1 sheet out of many and is not sheet that the Macro actually runs from. This sheet only shows the placement of data presently. I am wanting to change the locations to the area in blue above

If you need the other sheets I can provide a link or if you can change the Macro I can try it out and let you know

Thank you

Option Explicit

Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
    x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
    If (x(i, 1)) = "Game" Then j = j + 1
    If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
        With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
            .Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)
        End With
    End If
Next i
End Sub

Sub ClearGames()
Dim wsh As Worksheet, r As Range
For Each wsh In ThisWorkbook.Sheets
    If Not wsh Is ActiveSheet Then
        For Each r In wsh.Columns(1).SpecialCells(2).Areas
            r.Resize(, 91).Offset(1).CLEAR
        Next
    End If
Next wsh
End Sub

I am also getting an error it runs a while ans uncertain why

Area J.jpg

[SamT]

There is no "Ball" in that Code.

What is a "Ball?"

How does this "Ball" thing relate to the code.

I looked all over the Excel and the VBA Help Files and I could not find a "Ball" Function, Object, Method, Property, or Event anywhere

[MrSams]

To be an American member of the 9⁴ Group you seriously could not figure out that the word "Ball" was just titles in a row (-:
No different than your Number 1, Number 2, Number 3 .......
The data or title in the row is insignificant, its the row number I am looking to place the data

im not the vba nor excel expert. Does anything in this code above say anything about row placement of data? What does this code mean?

thank you

[SamT]

To be an American member of the 9⁴ Group you seriously could not figure out that the word "Ball" was just titles in a row (-:

Uh. . . nope. We speek an' tink VBA heer, and thet ain't VBA. I see from looking at the bottom of the page that people from 4 different countries have viewed your issue, possibly to see if they can help. You can continue to 'speak' as if we all fully understand your Game terminology, but it won't help your cause.

What does this code mean?

Sub ertert() 
    Dim x, i&, j& 
    With Sheets("Counter Totals") 
        x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value 
    End With 
    For i = 1 To UBound(x) 
        If (x(i, 1)) = "Game" Then j = j + 1 
        If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then 
            With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2) 
                .Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0) 
            End With 
        End If 
    Next i 
End Sub

Dim = Declare a variable so the Compiler can assign a memory location for the relevant value.
Dim x = Declare x as a Variant Type variable, which can be used as an array
Dim i&, j& = Declare i and j as Long Type variables, which can hold numbers large enough to Count Rows and Columns

x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
Set x = to a two dimensional array of all the values in the Range, where x(i) is the Row number and the second parameter, [x(i, 1),] is the first column in the i Row

For i = 1 to Ubound(x)
Upper Boundary of x is the last used Row number, (in column 1,) so it's the same as "For each Used Row on the Sheet in Column A"

IsNumeric(x(i, 1))) * (Len(x(i, 1))
The * symbol or Mutliplication symbol is, in this case, the same as the Boolean operator "And". This is because zero is Boolean False and all other numbers are Boolean True.
IsNumeric returns a Boolean if the value parameter [x(i, 1),] is a number
Len is the VBA String Length function
Len(x(i, 1) is not zero if x(i, 1) is not ""
If x(i, 1).value is zero, It is still numeric,(Boolean True,) but if Excel doesn't show zeros, the Length is also zero, (Boolean False.)
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) can be thought of as If IsNumeric And Not Zero

If (x(i, 1)) = "Game" Then j = j + 1
J is zero when Dimmed, so the first j encountered will be j = 1

If x(Row i, Column A) is a number other than zero.
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
With Sheets(GameNumber), Column A, Cells that are not Formulas

I presume that the Parameter Sets in the GameNumber sheet(s) have a blank row above them.

An Area is a rectangular set of used Cells bordered by empty Cells. Areas is a collection Object containing all such sets
.Areas(j)
The Jth set of used cell bordered by empty cells
.Areas(j)(.Areas(j).Count + 1, 1)
(.Areas(j).Count + 1, 1) looks like a Cells assignment, so .Areas(j).Count + 1 is the Row below that Area, and the Second Parameter is the Column Number

.Resize(, 91)
Resize that Cell on that game sheet to a Range 91 Columns Wide.

.Value = Application.Index(x, i, 0)

.

[MrSams]

I've figured this out. All I needed to do was move those rows. The code knew how to find the next row character automatically