Macro help in Excel please

Blakespops

Hi All, Firstly May I thank you all for taking time to read my problem and a huge Thanks to the people with solutions to my issue.

I have a macro running in excel that my unfiltered data is more than 1048576 lines thus times out. is it possible to have it continue in the adjoining columns to complete it's initial calculations it is now placing in columns H-M(H2-M2 all have separate values relating to columns A2-F2 continuing in H-m do max out). So maybe continue in columns O-T then V-AA etc. as required?

I then will wish to use a filter so these results can be filtered in many different ways placing their results on sheets 2-14 such as results with no grater than two consecutive values, results then from sheet two that contain no grater tan 3 odd values, no duplicate values, and each sheet will then have another filter cutting down each set of results allowing me to gain the totals for how many each filter eradicated.

God bless for your help with the first part of my issue. Below is the Macro code I have running.

Sub Tote6()

Dim ws As Worksheet
'the data worksheet
Dim vArr1 As Variant, vArr2 As Variant, vArr3 As Variant
Dim vArr4 As Variant, vArr5 As Variant, vArr6 As Variant
'arrays to hold data from the ranges listing the horses
Dim lCount1 As Long, lCount2 As Long, lCount3 As Long
Dim lCount4 As Long, lCount5 As Long, lCount6 As Long
'counters for looping through arrays
Dim lRow As Long
'counter for row output

Set ws = ThisWorkbook.Worksheets("Sheet1")
'the worksheet we will be using

With ws
vArr1 = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
vArr2 = .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row).Value
vArr3 = .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row).Value
vArr4 = .Range("D2:D" & .Cells(Rows.Count, 4).End(xlUp).Row).Value
vArr5 = .Range("E2:E" & .Cells(Rows.Count, 5).End(xlUp).Row).Value
vArr6 = .Range("F2:F" & .Cells(Rows.Count, 6).End(xlUp).Row).Value
End With
'populate the arrays with data from the ranges

Application.ScreenUpdating = False
'switch off during output

ws.Columns("H:M").Clear
'delete previous output
ws.Range("H1:M1").Value = "Header"
'add header line for filtering
lRow = 1
'reset counter

For lCount1 = LBound(vArr1, 1) To UBound(vArr1, 1)
For lCount2 = LBound(vArr2, 1) To UBound(vArr2, 1)
For lCount3 = LBound(vArr3, 1) To UBound(vArr3, 1)
For lCount4 = LBound(vArr4, 1) To UBound(vArr4, 1)
For lCount5 = LBound(vArr5, 1) To UBound(vArr5, 1)
For lCount6 = LBound(vArr6, 1) To UBound(vArr6, 1)
lRow = lRow + 1
'increment row counter for output
With ws
.Cells(lRow, 8).Value = vArr1(lCount1, 1)
.Cells(lRow, 9).Value = vArr2(lCount2, 1)
.Cells(lRow, 10).Value = vArr3(lCount3, 1)
.Cells(lRow, 11).Value = vArr4(lCount4, 1)
.Cells(lRow, 12).Value = vArr5(lCount5, 1)
.Cells(lRow, 13).Value = vArr6(lCount6, 1)
End With
'record each combo in columns 8-13 (H-M)
Next lCount6
Next lCount5
Next lCount4
Next lCount3
Next lCount2
Next lCount1
'loop through arrays to produce combinations

ws.Columns("H:M").EntireColumn.AutoFit
'adjust display
Application.ScreenUpdating = True
'switch on again

MsgBox "Combinations counted : " & lRow - 1
'show count of combinations

End Sub

Mirno

You've got 6 nested for loops - is this correct?

Your data will look like at the end
A2,B2,C2,D2,E2,F2
A2,B2,C2,D2,E2,F3
A2,B2,C2,D2,E2,F4
...
A2,B2,C2,D2,E2,F<whatever>
A2,B2,C2,D2,E3,F2
A2,B2,C2,D2,E2,F3
...
A<whatever>,B<whatever>,C<whatever>,D<whatever>,E<whatever>,F<whatever>

So you'll end up with 1048576^6 - Or 1.32E36 rows - which won't be indexable by an integer, and would fill your computer memory several times over even if it did.

Mirno

cookie365

I really don't have a clue what the macro is trying to do, either from the code or your description.

But I agree with Mirno about the effect of the nested loops.

If it was possible to complete the code, I think the spreadsheet itself would become sentient. I for one welcome our new pivot table overlords!

WTFH

Explain in English what you are wanting to use the spreadsheet for.

If this is one of your betting things, then it could be interesting to try to work out. (Although I'm not certain why someone with your signature would be gambling)

TeamSharb

WTFH wrote: »

(Although I'm not certain why someone with your signature would be gambling)

Because there is no prophet in atheism?

securityguy

Mirno wrote: »

So you'll end up with 1048576^6 - Or 1.32E36 rows - which won't be indexable by an integer,

"several" being the understatement of the century. 1048576^6 is 2^120 (it's (2^20)^6).

RAM is about 8 pounds a gigabyte. Assuming you get the world's biggest bulk discount, let's pretend you can buy it for a pound a gigabyte. 2^64 bytes, the maximum addressable by a modern processor, will therefore cost you 2^34 pounds, or about £17 billion.

Our friend would need 2^(120-64) of these £17bn computers. 2^56 is about 72 thousand trillion.

Spending £1329227995784915872903807060280344576 doesn't seem a very MSE way to go about building a spreadsheet.

StuC75

Use access and write to a table instead? certainly for that volume of data..

Blakespops

For all the people who just seem to attack others on here questioning their need to use the information rather than simply offering it up, I am autistic and when I have an idea I have to work it out and what I am doing has nothing to do with asking how you can get macro to switch to other columns so it completes the task asked of it. Yes it will be a lot of digits when completed but as I have many books saved on my computer sticks this should be a lot easier and as for how many the final totals will be as each calculation's input changes sometimes the system will be starting with 5, 7,8,or less per starting point.

andydiysaver

the guys on here have a point about the nested for next loops


looks like you're using the code to scrape rapidly updating data from a third party app feeding it in - trouble is, it's an extremely inefficient system


if I were you I'd concentrate on a few cells within the actual spreadsheet to nail down the data you want and only the data you want, then freeze it, then scrape it.


you're limited by the size of the sheet excel side, and the size of the arrays and the counters VBA side.

andydiysaver

edit- and of course it's gambling - this is data manipulation of third party data from horses, attempting to freeze it gain an edge and slap it on and profit via means of Kelly equation or otherwise - note this is NOT judgement - was into it myself for years, developed countless VBA odds scrapers, realised it was a losing game.

WaywardDriver

Like the others not sure what you're trying to do but if you want to generate all combinations then may be possible without macro.

1. in H1 enter =COUNTA(A2 : A999) to get number of non-blank cells in column A (may need to change the 999 if more) and copy to M1
2. in N1 enter =PRODUCT(H1:M1) (number of combinations)
3. enter the number 1 in each of the cells H2-M2
4. in H3 enter =IF(I3<I2,IF(H2<H$1,H2+1,1),H2) and copy to L3
5. in M3 enter =IF(M2<M$1,M2+1,1)
6. copy H3-M3 down to row number = N1 + 1
7. this gives the unique combinations 111111, 111112 etc
8. to get the actual values in columns A-H enter =INDIRECT("A" & H2+1) in N2 and extend

As others have said the number of combinations may quickly escalate to exceed Excel's capacity depending on the number of values in columns A-F.