Need help with VBA Error

[MrSams]

I decided to start a new thread with better instructions hopefully. Presently we Place draw numbers in the Input sheet I2:O101
Draw numbers are copied from Drawn Number sheet 1 at a time and placed into “any” of the rows on the Input sheet in I2:O101. Presently the Macro “only” runs in row 49 (Game3) Present Macro called NumbersGrabber works as instructed with the exception of “only Game3 and needs to work when numbers start in “any” row on the Input sheet. This is the Macro


Sub NumbersGrabber()
Dim InputSht As Worksheet
Dim DrnNumSht As Worksheet
Dim i, r As Long
Dim LastRowFound As Boolean
Set InputSht = Sheets("Input")
Set DrnNumSht = Sheets("Drawn Number ")

LastRowFound = False
r = 1
Do
r = r + 1
If Not WorksheetFunction.IsNumber(DrnNumSht.Cells(r, "I")) Then
LastRowFound = True
r = r - 1
End If
Loop Until LastRowFound

For i = r To 2 Step -1
DrnNumSht.Cells(i, "I").Resize(, 7).Copy
InputSht.Range("I49").Resize(, 7).Insert shift:=xlShiftDown
ertert
Next i
End Sub



At completion of each set of numbers copied from the Drawn Number sheet to the Input sheet or this Macro above, numbers go to external sheets for additional calculations and returns letters and numbers to sheet “Counter Totals 1-50” to its appropriate locations i.e. (E-O1,EO1,EEOO1,50+E-O1,50+EO1,50+EEOO1,OBE-O1,OBEO1,OBEEOO1) (E-O2,EO2,EEOO2,50+E-O2,50+EO2,50+EEOO2,OBE-O2,OBEO2,OBEEOO2) (E-O3,EO3,EEOO3,50+E-O3,50+EO3,50+EEOO3,OBE-O3,OBEO3,OBEEOO3) (E-O4,EO4,EEOO4,50+E-O4,50+EO4,50+EEOO4,OBE-O4,OBEO4,OBEEOO4) (E-O5,EO5,EEOO5,50+E-O5,50+EO5,50+EEOO5,OBE-O5,OBEO5,OBEEOO5) ) (E-O6,EO6,EEOO6,60+E-O6,60+EO6,60+EEOO6,OBE-O6,OBEO6,OBEEOO6)

All the mentioned above works flawlessly as a result of the 1st Macro NumbersGrabber, this Macro allows the above to happen through other linking steps. These are then copied to the: “Counter Totals” sheet


Now for the part that does NOT work correctly:
Once the above happens we need the numbers and letters from the Counter Totals sheet to be copied and pasted to the appropriate Game number 3-4-5-6-7-8-…. And its proper location (E-O1,EO1,EEOO1,50+E-O1,50+EO1,50+EEOO1,OBE-O1,OBEO1,OBEEOO1) (E-O2,EO2,EEOO2,50+E-O2,50+EO2,50+EEOO2,OBE-O2,OBEO2,OBEEOO2) (E-O3,EO3,EEOO3,50+E-O3,50+EO3,50+EEOO3,OBE-O3,OBEO3,OBEEOO3) (E-O4,EO4,EEOO4,50+E-O4,50+EO4,50+EEOO4,OBE-O4,OBEO4,OBEEOO4) (E-O5,EO5,EEOO5,50+E-O5,50+EO5,50+EEOO5,OBE-O5,OBEO5,OBEEOO5) ) (E-O6,EO6,EEOO6,60+E-O6,60+EO6,60+EEOO6,OBE-O6,OBEO6,OBEEOO6) which also works but I get this error and do not know what to look for or how to fix it.
Here is the present Macro:




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


When the above runs I get this error:

17283

and this area is hi-lighted as a result of this error:

17284

What will cause this error and how do I go about to fix it or does the Macro need to be rewritten? Please keep in mind my VBA experience is none at all but learning as I go when I have the time. Let me know if you need any additional information. Thank you

Spreadsheet example:

[Paul_Hossler]

I think it's because there is no worksheet named "Game50"




Option Explicit

Sub ertert()

Dim x As Variant, i As Long, j As Long

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
End If
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then

MsgBox x(i, 1) ' = 50
MsgBox "Game" & x(i, 1) ' = "Game50" which doesn't exist

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

[MrSams]

The actual book does have a Game50 and Game#50 in it but they were hidden. Is there a way to step through the Macro and it point to the direction of the error?

[Paul_Hossler]

I don't think there is a worksheet named "Game50" there


17286

[MrSams]

Perhaps its set up wrong IDK, Id be glad to provide a link for the book but he does not want it up long for the world to download

17287
17288

[MrSams]

Let me know when you have this uploaded so I can take the link down please

[Paul_Hossler]

OK I have it

I'll take a look

[Paul_Hossler]

Like I said, I don't have time to go through the logic and see what the entire macro is doing, but

x is a 1-299 x 1-91 two dimensional array, so there is not a 0-th element

I don't think you need to use .Index and instead of x(I,0), I think you just want to use x(I,1)

like this



.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = x(i, 1) ' Application.Index(x, i, 0)



But I still think there are fundamental logic errors

After it gets past the above, I get a 1004 error

J = 3, but there are only 2 areas in the .SpecialCells


17289

[MrSams]

I've made the suggested change but I don't understand what to do with area in red

17290

[Paul_Hossler]

The lower pane is called the "Immediate Window" (shortcut key Control-G)

You can execute (some) commands immediately (e.g. Application.ScreenUpdating = True + <enter>)

You can also get the values of variables by using a ?+<variable>

For example I entered ?Sheets("Game" & x(I,1)).Columns(1).Specialcells(2).Address and got the address back on the next line and saw that there were only 2 areas (separated by a comma)

?j showed me that the program was asking for the third area -- .Areas(3) -- which didn't exist

That's why I suggested that the logic might be flawed in that area since




If (x(i, 1)) = "Game" Then j = j + 1


doesn't seem like it would have anything to do with the number of areas in .Columns(1) of any given sheet

I could be 100% wrong since I have not looked at what the entire macro does

[MrSams]

I'm clueless and have a lot to learn. Thanks for looking at this

[Aussiebear]

I'm clueless and have a lot to learn. Thanks for looking at this

Then my advice, is to ask the originator of the workbook to confirm with you what they meant by .Areas(3). Obviousily they had some kind of intention when they constructed the code.

[MrSams]

Aussie,

i just asked him, he has no idea. I found the code and sheets and is trying to get them to work.

is it possible to start new code based upon what is none in my 1st post?

[Paul_Hossler]

Some of the complexities I've noticed that that there are 50 "Game" sheets and 50 "Game#" sheets, 'Drawn Number' and Drawn Numbers', 'Input', 'Counter Totals' and Counter Totals 1-50'

Some sheets are hidden and/or protected

The 'GameX' series has 6 blocks with EO1, E-O1. EO2, ... OBEEOO1 and the 'Game#X' series has 4 or 5 block with cryptic headings and references to an external link:

='D:\Lotto Games\Lotto Texas\Lotto Texas Reset to Game 1\[Lotto Texas RESET 1 (Numbers Book).xlsx]UD-EO#1'!W3

'Input' starts at Game=50 with lots of #NUM errors and goes down to Game -50


I'm guessing (bold, underlined) that much of the above complexity is using WS formulas to calculate intermediate results


I'm also guessing that the last 50 Texas Lotto games are being analyzed, so I'd start with the sheet that has the 50 sets of number, define what report(s) I want for a 'Game', and summary report(s) for all 50 games

A macro that takes Game(x)'s 6 numbers and does something with them is not hard IF, IF, IF, IF the requirements are clearly defined. The originator is probably (hopefully) the only person who knows what OBEEOO1 means

[MrSams]

Paul,

i believe your looking at this way too hard with complexity. We only need to take what is on the counter Totals sheet for example Game 3 (Rows 50,100, 150,200,250,300) and distribute those answers to the Game3 sheets proper location (rows 3,2501,4001,6001,8001,10,001)

looking at the numbers in column A (Input or Counter Totals sheet ) those are Game numbers. So Game 3 is in rows 3,50,100,150,209,250,300. Game 25 is in rows 28,75,128,178,228,278 and so on. Game 25 would go to the Game28 sheet for distribution just like the Game3 sheet or any other Game sheet

knowing "what" is EEOOBB doesn't matter. We're only wanting to copy the data from one sheet to the next. Each time a new draw is entered on the Input sheet new data appears in the Counter Totals sheet Game3 rows 50,100,150,200,250,300. The new draw will go to the actual Game3 sheet under the last. So the new draw goes in row 4,2502,4002,6002,8002,10,002. The next will go into rows 5,2503,4003,6003,8003,10,003 and so on

[MrSams]

In order for Our code to have more rows between sets of games: We just insert enough rows (like 2,950 rows) to move row 50 down to row 3000 - or even down to row 30000. The code is set to work on areas of constant values, so as long as we don't enter anything between our values were okay.



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



That basically says "Find all the blocks of words and numbers in column A and enter the new values in the first blank row beneath each of those blocks."


This is my understanding of the code from him and I hope it makes sense to you guys

[Paul_Hossler]

Well, my definition of 'Complexity' is a 16 MB workbook with 2 groups each with 50 identical worksheets containing 30,000 rows position-dependent and 90 position-dependent columns that I can see some grad student not looking at

Anyway ....

To make the ertert macro work, I changed the macro to use the suggestion from post #20 in

http://www.vbaexpress.com/forum/showthread.php?57323-Whats-wrong-with-my-code-copy-paste-does-not-change-Array-formula




Sub ertert()
Dim x As Variant, i As Long
Dim r As Range
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 Not IsEmpty(x(i, 1)) Then
If IsNumeric(x(i, 1)) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
For Each r In .Areas
r.Resize(, 91).Value = Application.Index(x, i, 0)
Debug.Print r.Address(1, 1, 1, 1) & " --- " & r.Resize(, 91).Address(1, 1, 1, 1)
Next
End With
End If
End If
Next i
End Sub



You can comment out the Debug statement, but when you run the macro, 1) it runs to completion without errors, and 2) generates data as below. You'll have to tell if it's correctly getting the right data, and putting it in the right place

17295

[MrSams]

OMG, almost there Paul, just need to drop down a row on the Game sheets. I only ran 2 draws but will run more once this is fixed and let you know but it certainly looks like it may do the trick (-:

17297

[Paul_Hossler]

Try this then. Changed the line marked




Sub ertert()
Dim x As Variant, i As Long
Dim r As Range
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 Not IsEmpty(x(i, 1)) Then
If IsNumeric(x(i, 1)) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
For Each r In .Areas
r.Offset(1, 0).Resize(, 91).Value = Application.Index(x, i, 0) '------------------------------
Next
End With
End If
End If
Next i
End Sub

[MrSams]

Sorry for the delay but it's taken this long to run 150 rows . This works PERFECTLY . Paul you ARE the man.

is it possible to look at the 1st Macro (Grabber) and give us the ability to place data in any row (row 2-50) and gave the data distributed to the game sheets they reflect? Presently we place numbers from row 49-101 which is Game 3-2-1 and since we have a sheet for Game 3 the 2 nd Macro places that data to Game3. We would like to be able to use any Game if possible.

so if we place data in row 2-101 it puts data in Game3 through Game50, the 2nd Macro (one you fixed) will distribute data to Games3 through Game50 , then the Grabber Macro does its thing by taking drawn numbers to the Input sheet the same way it wirks now for ONLY Game3 and steps down the new numbers in all rows

[Paul_Hossler]

I took a look at NumbersGrabber for you, and ran through 1 or 2 loops

I put some unscientific timing measurements in

Each of The .Insert takes 35 seconds, and the call to ertert takes 47 (Win10Pro, 4 core, 3.4GHz)

There are 1605 rows in 'Drawn Number'

So (1605 rows) x (82 sec/row) x (1 min/60sec) x (1 hr/60min) = 36.6 hours

I was going to turn off calculation, but it seems so many cell that are used depend on formulas being updated that I didn't think that work work

I'm guessing the reason the .Insert takes so long to do a simple 7 cell insert is because so many cell addresses need to be updated and so many cells need to be recalculated

Probably the same for the ertert call



Option Explicit
Sub NumbersGrabber()
Dim InputSht As Worksheet
Dim DrnNumSht As Worksheet
Dim i As Long, r As Long
Dim t1 As Date, t2 As Date, t3 As Date

Set InputSht = Sheets("Input")
Set DrnNumSht = Sheets("Drawn Number")

r = DrnNumSht.Cells(DrnNumSht.Rows.Count, 9).End(xlUp).Row

MsgBox r - 1 ' header row

For i = r To 2 Step -1
DrnNumSht.Cells(i, "I").Resize(, 7).Copy
t1 = Now
InputSht.Range("I49").Resize(, 7).Insert shift:=xlShiftDown
t2 = Now
ertert
t3 = Now

MsgBox (t2 - t1) * 24 * 60 * 60
MsgBox (t3 - t2) * 24 * 60 * 60


Next i
End Sub

'caused from copy/pasting too much
Sub DeleteUnneededStyles()
Dim i As Long

For i = ThisWorkbook.Styles.Count To 1 Step -1
Application.StatusBar = i
On Error Resume Next
If Not ThisWorkbook.Styles(i).BuiltIn Then ThisWorkbook.Styles(i).Delete
On Error GoTo 0
DoEvents
Next i

Application.StatusBar = False
End Sub

[MrSams]

Paul,

can you go back and read my request :-). I know why it takes so long. Thanks

im wanting the ability to do more than Game3

[Paul_Hossler]

I did read the request, but as I said several times, I don't understand the macros or what it is you really want to do in terms of Excel.

I'm sure you know how you want to evaluate what you're doing but


is it possible to look at the 1st Macro (Grabber) and give us the ability to place data in any row (row 2-50) and gave the data distributed to the game sheets they reflect? Presently we place numbers from row 49-101 which is Game 3-2-1 and since we have a sheet for Game 3 the 2 nd Macro places that data to Game3. We would like to be able to use any Game if possible.

so if we place data in row 2-101 it puts data in Game3 through Game50, the 2nd Macro (one you fixed) will distribute data to Games3 through Game50 , then the Grabber Macro does its thing by taking drawn numbers to the Input sheet the same way it wirks now for ONLY Game3 and steps down the new numbers in all rows


doesn't help someone who doesn't know what you know

I don't mind try to help with Excel concepts or questions, but NumbersGrabber sub doesn't reference any 'Game...' or 'Game#...' sheets so it must be buried somewhere in the sub 'ertert'.

I have NO idea how to put any data on any of the Game worksheets, or where the data goes or what happens when it gets there.

I don't know why you're going through all 1605 sets of numbers, when there only seems to be 50 separate 'Games'

Since each iteration seems to take 80 - 90 seconds, that adds to the stand-around (wall clock) time for testing

There's just too much I don't know to even attempt it