MrSams
10-02-2016, 10:00 PM
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
17243
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
17243